ANT sql task: How to run SQL and PL/SQL and notice execution failure?

Question

to execute an .sql script file from ANT it works fine using the following task:

<sql
    classpath="${oracle.jar}" driver="oracle.jdbc.OracleDriver"
    url="jdbc:oracle:thin:@@@{db.hostname}:@{db.port}:@{db.sid}" 
    userid="@{db.user}" 
    password="@{db.password}"
    src="@{db.sql.script}" />

But if the .sql file not only contains pure SQL but also PL/SQL the task will fail. This could be solved by using the following snippet:

<sql
    classpath="${oracle.jar}" driver="oracle.jdbc.OracleDriver"
    url="jdbc:oracle:thin:@@@{db.hostname}:@{db.port}:@{db.sid}" 
    userid="@{db.user}" 
    password="@{db.password}"
    delimiter="/"
    delimitertype="row"
    src="@{db.sql.script}" />

But if my script contains both SQL and PL/SQL then neither ANT task will work. Another solution would be to use the "exec" task with "sqlplus":

<exec executable="sqlplus" failonerror="true" errorproperty="exit.status">
    <arg value="${db.user}/${db.password}@${db.hostname}:${db.port}/${db.sid}"/>
    <arg value="@${db.sql.script}"/>
</exec>

But unfortunately this task will never fail, hence the build returns always with "SUCCESSFUL" even though the sql script execution failed. The error property which I tried to set would not return any error code.

Any ideas/suggestions how to solve this problem?

Thanks,

Peter

Answer 1

Peter,

Add at the beginning of scripts

  WHENEVER SQLERROR EXIT SQL.CODE;

Then sqlplus will exit with exit code != 0.