ANT sql 任务:如何运行 SQL 和 PL/SQL 并注意执行失败? [英] ANT sql task: How to run SQL and PL/SQL and notice execution failure?

问题描述

要从 ANT 执行 .sql 脚本文件，它可以使用以下任务正常工作:

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}" />

但如果 .sql 文件不仅包含纯 SQL，还包含 PL/SQL，则任务将失败.这可以通过使用以下代码段来解决:

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}" />

但是如果我的脚本同时包含 SQL 和 PL/SQL，那么这两个 ANT 任务都不起作用.另一种解决方案是将exec"任务与sqlplus"一起使用:

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>

但不幸的是，这个任务永远不会失败，因此即使 sql 脚本执行失败，构建也总是返回成功".我尝试设置的错误属性不会返回任何错误代码.

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?

谢谢，

彼得

推荐答案

我想有点晚了 - 但我希望这会帮助某人:

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

总的来说，我认为我们应该更喜欢使用 sql 而不是 exec executable="sqlplus" ，原因有很多，例如:如果我们更改了数据库提供程序，您就不会在使用 sql 的新进程中花费资源，"STOPPING" 与 sqlplus.exe 等相反.

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.

无论如何，这里有一个关于如何让 PL/SQL &SQL 在同一个脚本中，以便它可以工作:

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

myScript.sql:

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>

然后将结果提供给:<sql delimeter="/" src="myScript.sql"/>

说明:如果您有常规的 sql 命令:

explaination: If you have regular sql commands:

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

它们将被转换为:

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

这是等效的 - 带有/"分隔符的 sql 命令可以处理它们.

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

另一方面，

BEGIN
  blah
END;
/

将转换为:

BEGIN
  blas
END/
/

并使用第二个正则表达式 - 转换回

and using the second regex - transformed back to

BEGIN
  blas
END;
/

所以每个人都赢了！万岁！

So everybody wins! Hurray!

祝你好运.

这篇关于ANT sql 任务:如何运行 SQL 和 PL/SQL 并注意执行失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！