Best practices

Use an entrance SQL script

When running SQL scripts through SQL*Plus, one may frequently use the same options (e.g. spool file, echo on etc). Therefore it might be worth to summarize those options within one entrance script that is used to jump-start the actual SQL script.

Such an entrance script could look like: 

---------------- exec.sql ----------------------         
set pages 21 lines 80 term on echo off verify off feedb on 
whenever oserror exit failure 
whenever sqlerror exit sql.sqlcode

spool &2 append

start &1

This script takes 2 start parameters:

1) the script file name to be started.

2) the log file name

Define an Ant macro to wrap the sqlplus launch

Now it's useful to define an Ant macro that calls this exec.sql and simplifies the actual SQL launch within the Ant build file. 

<macrodef name="execsql">
    <attribute name="start" />
    <attribute name="dir" default="${sql.core.dir}"/>
    <attribute name="properties" default="db.exec.properties"/>              
    <attribute name="logfile" default="sql.log"/>
    <sequential>
        <ora:sqlplus dir="@{dir}"
             start="exec.sql" silent="true" failonerror="true"
             failOnSpoolMatch=".*SP2-[0-9]{4}.*">
            <arg value="@{start}"/>
            <arg value="@{logfile}"/>
            <propertyset refid="@{properties}"/>
        </ora:sqlplus>
    </sequential>
</macrodef>

Through this macro, we are able to start a SQL script like this: 

 <execsql start="schema.sql"/>          

 <execsql start="script.sql" dir="mydir"/>

Pass Ant properties into SQL

Since 0.2.2 it is possible to pass Ant properties into SQL*Plus and use them as defined variables inside the SQL script (see this article).

For instance one can define a property set including all properties with the prefix "db", like: 

<property name="db.user" value="scott" />          
<property name="db.pass" value="tiger" />          
<property name="db.local" value="mydb" />          
<propertyset id="db.allproperties">
    <propertyref prefix="db." />
</propertyset>
and use these properties within SQL: 
---------------- script.sql ----------------------         
connect &&db_user/&&db_pass@&&db_local         
select * from all_users 
 where username = upper('&db_user');
by calling the macro, mentioned above: 
<execsql start="script.sql" properties="db.allproperties"/>