SQLPLUS and the Ant build tool...

I've been looking for ways to automate running of Oracle SQLPlus PL/SQL scripts.Of course there are always batch and shell scripts but I'm looking for something less OS specific.Our Java build process is controlled by Apache Ant. It would be nice to include running of SQL scripts in this process. Here are some tools to help integrate SQLPLUS with ANT.

SQL Task
This task is best for executing bits of literal SQL. This task doesn't know anything about SQLPlus features such as formatting, variables, spooling, etc.

More information on this task is available here: http://ant.apache.org/manual/CoreTasks/sql.html

Exec Task
If you rely on SQLPLUS constructs you can use Apache's Exec to create a basic task for firing up SQLPLUS to run a script:

<project default="sample">
<target name="sample"
    description="A basic target">
<exec executable="sqlplus"
    failonerror="true">
  <arg line="-s scott/tiger@tns @script.sql"/>
</exec>
</target>
</project>

If you have WHENEVER SQLERROR EXIT 1 at the top of your SQL Script, SQLPlus will exit and return a "1" return code to Ant which will be caught by the failonerror="true" and dump out of the build.

Ant is Extensible - New Ant Tasks
The nice thing about Ant is that its extensible. If there is a process in your build that is not supported you are free to write your own task to handle it. So far the Exec task above is working OK for my purposes, but I have found two other

Basic SQLPlus Task by Leonardo Junquera
In the 7/28/2000 Ant Development mailing list archives there is a post from Leonardo Junquera with source code for a SQLPlus task that can be added to Ant: http://marc.info/?l=ant-dev&m=86951893308026&w=2

Incanto - http://incanto.sourceforge.net/
Incanto contains a set of Apache Ant tasks that makes Oracle database utilities accessible from Ant's XML build file.

An article about using it is here: https://mauszeig.wordpress.com/2006/04/10/jump-start-oic-incanto/

Want to get really fancy?

Using the following method you can read properties from a database for use in an Ant build.xml file: link

What's next?
Now that I know we can deploy via ANT and it will stop when it encounters an error I get to write a system to organize batches of changes for deployment. Woo hoo!

– Dave