ANT sql task: How to run SQL and PL/SQL and notice execution failure?
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
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
blah
END;
/
will be transformed to:
BEGIN
blas
END/
/
and using the second regex - transformed back to
BEGIN
blas
END;
/
So everybody wins! Hurray!
Good luck.
链接地址: http://www.djcxy.com/p/44340.html