I was looking for the way how to deploy Oracle PL/SQL from ant or execute more sql commands that are specified in file. Solution is quite simple, but I did’nt find it in Google.
The trick is to use two attributes of the <sql/> task. This attributes are:
delimiter="/"– this specifies delimiter of the commandsdelimitertype="row"– sthis specifies that the delimiter is row type.
Example of the ant task is
<target name="installPLSQL" depends="check-fab">
<echo message="INSTALLING PL/SQL: ${db.driver} ${db.url}" />
<sql driver="${db.driver}" url="${db.url}" userid="${db.user}"
password="${db.pw}" onerror="continue"
print="true" delimiter="/" delimitertype="row">
<classpath refid="master-classpath" />
<fileset dir="plsql">
<!-- compile package specification first -->
<include name="package_h.pls"/>
<!-- compile package body -->
<include name="package_b.pls"/>
</fileset>
</sql>
</target>
Example of the package_h.pls is
CREATE OR REPLACE PACKAGE myPackage AS
...
END;
/
Example of the package_b.pls is
CREATE OR REPLACE PACKAGE BODY myPackage AS
...
END;
/
You can also create tables or execute more SQL commands from the file. Only need is to use / (or other delimiters, but slash is the standard in SQL*Plus) to separate the commands.
CREATE TABLE myTable1 (... );
/
CREATE TABLE myTable2 (... );
/