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

Peter,

Add at the beginning of scripts

  WHENEVER SQLERROR EXIT SQL.CODE;

Then sqlplus will exit with exit code != 0.

Pretty late, I guess - but I hope this will help someone:

In general, I think we should perfer using sql rather than exec executable="sqlplus" for many reasons, such as: in case we change DB provider, you don't spend reaources in a new process with sql, "STOPPING" will work as opposed to sqlplus.exe etc.

Anyway, here's a suggestion on how to let both PL/SQL & SQL in same script so that it will work:

myScript.sql:

<copy todir="...">  <fileset dir="...." includes="myScript.sql"/>  <filterchain>    <replaceregex byline="false" pattern=";" replace="{line.separator}/" flags="mg"/>    <replaceregex byline="false" pattern="/[\s]*/" replace=";${line.separator}/"  flags="mg"/>   </filterchain></copy>

then give the result to: <sql delimeter="/" src="myScript.sql"/>

explaination:If you have regular sql commands:

drop table x;select blah from blue where bli=blee;

They will be transformed to:

drop table x/select blah from blue where bli=blee/

which is equivlant - and the sql command with "/" delimeter can handle them.

On the other hand,

BEGIN  blahEND;/

will be transformed to:

BEGIN  blasEND//

and using the second regex - transformed back to

BEGIN  blasEND;/

So everybody wins! Hurray!

Good luck.