Ant中执行oracle存储过程

xfavuqrqos

同样的oracle dll脚本:
declare c int; begin
select count(*) into c from user_tables where table_name = upper('ACTIVESTARTTIMES');if c = 1 then execute immediate 'drop table "ACTIVESTARTTIMES" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('BASEEVENTFORMAT_CLASSIFIERS');if c = 1 then execute immediate 'drop table "BASEEVENTFORMAT_CLASSIFIERS" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('BASEEVENTFORMAT_PAYLOADIDS');if c = 1 then execute immediate 'drop table "BASEEVENTFORMAT_PAYLOADIDS" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('BASEEVENTFORMAT');if c = 1 then execute immediate 'drop table "BASEEVENTFORMAT" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('CLASSIFIERBEFMAP');if c = 1 then execute immediate 'drop table "CLASSIFIERBEFMAP" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('CLASSIFIERGROUPS');if c = 1 then execute immediate 'drop table "CLASSIFIERGROUPS" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('CLASSIFIER');if c = 1 then execute immediate 'drop table "CLASSIFIER" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('CLQUERYMODEL');if c = 1 then execute immediate 'drop table "CLQUERYMODEL" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('ECOMPID');if c = 1 then execute immediate 'drop table "ECOMPID" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('LGGINGPOLICYMESSAGE_PROPERTIES');if c = 1 then execute immediate 'drop table "LGGINGPOLICYMESSAGE_PROPERTIES" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('LOGGINGPOLICYMESSAGE');if c = 1 then execute immediate 'drop table "LOGGINGPOLICYMESSAGE" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('LOGGINGPOLICYMODEL_MESSAGES');if c = 1 then execute immediate 'drop table "LOGGINGPOLICYMODEL_MESSAGES" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('LOGGINGPOLICYPROPERTY');if c = 1 then execute immediate 'drop table "LOGGINGPOLICYPROPERTY" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('LOGSERVICE');if c = 1 then execute immediate 'drop table "LOGSERVICE" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('OTHERSITUATION_ANY');if c = 1 then execute immediate 'drop table "OTHERSITUATION_ANY" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('PAYLOADDATA');if c = 1 then execute immediate 'drop table "PAYLOADDATA" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('PAYLOAD');if c = 1 then execute immediate 'drop table "PAYLOAD" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('REPORTINGCOMPONENTID');if c = 1 then execute immediate 'drop table "REPORTINGCOMPONENTID" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('SITUATIONTYPE');if c = 1 then execute immediate 'drop table "SITUATIONTYPE" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('STATSCLASSIFIERS');if c = 1 then execute immediate 'drop table "STATSCLASSIFIERS" cascade constraints';c:=0;end if;
select count(*) into c from user_tables where table_name = upper('STATSCLASSIFIERS');if c = 1 then execute immediate 'drop table "STATSCLASSIFIERS" cascade constraints';c:=0;end if;
end;


在Ant中运行出现错误如下
06/08/12 14:22:59.800 INFO: [ProcessWrapper] [STDOUT] cleanup.db.logging:
06/08/12 14:23:00.232 INFO: [ProcessWrapper] [STDOUT]       [sql] Executing resource: /root/installation/DSEngine/work/silver-ion-ui-1/scripts/cleanupDDL/oracle/drop_commonlogging.ddl
06/08/12 14:23:00.247 INFO: [ProcessWrapper] [STDERR]       [sql] Failed to execute:  declare c int; begin select count(*) into c from user_tables where table_name = upper('ACTIVESTARTTIMES');if c = 1 then execute immediate 'drop table "ACTIVESTARTTIMES" cascade constraints';c:=0;end if
06/08/12 14:23:00.267 INFO: [ProcessWrapper] [STDERR]
06/08/12 14:23:00.268 INFO: [ProcessWrapper] [STDERR] BUILD FAILED
06/08/12 14:23:00.268 INFO: [ProcessWrapper] [STDERR] java.sql.SQLException: ORA-06550: line 1, column 201:
06/08/12 14:23:00.268 INFO: [ProcessWrapper] [STDERR] PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
06/08/12 14:23:00.268 INFO: [ProcessWrapper] [STDERR]
06/08/12 14:23:00.268 INFO: [ProcessWrapper] [STDERR]    ;
从错误上看是一行结尾没有';', 可是检查了一下, 每一行结尾, 还有文件结尾的确都有';', 把脚本放到DbVisualizer中执行没有问题, 但在Ant中运行为什么会找不到呢?
在网上查了一下，原来Ant默认使用分号来作为一条SQL语句结束, 所以原来的存储过程中的语句被分开执行了。我们希望SQL仍做为整体运行, 可以将delimiter为"/"或其他符号(:= . ( @ % ; not之一), 这样存储过程中就没有分隔符, SQL语句可以作为整体运行了.
注意, 只有oracle需要这样, 如果是sqlserver则不必加delimiter.当然加上也不会有问题.

    <target name="cleanup.db.logging" depends="replace.db.password"
description="Run ddl to cleanup commonlogging tables in external database for amx administrator">
<sql
driver="${admin.db.driver}"
url="${admin.db.url}"
userid="${admin.db.username}"
password="${plaintext.password}"
print="yes"
delimiter="/">
<classpath refid="jdbc.classpath"/>
<transaction  src="./${db.ddl.path}/drop_commonlogging.ddl"/>
</sql>
</target>