TOMÁŠ HUBÁLEK BLOG: BAVTE SE PŘIMĚŘENĚ…

How to deploy Oracle PL/SQL or execute sql commands specified in file from Ant

Written By: Tomáš Hubálek - Oct• 08•04

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 commands
  • delimitertype="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 (... );
/

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

Your email address will not be published. Required fields are marked *