Using Ant properties within SQL scripts with Incanto’s <sqlplus> Task

In a previous article i wrote about a solution for passing values from the database via select statements to Ant properties inside Ant build files.

Now I’ve extended the Incanto <sqlplus> Task to do the other way round and pass Ant property sets into the SQL scripts as defined variables.

Within SQL*Plus it is possible to work with defined variables in the form of:

define myusername = SCOTT
define usertable = ALL_USERS
select * from &usertable where username='&myusername';

In case of running SQL scripts from Ant with Incanto one can pass single properties into SQL this way:

<property name="db.username" value="SCOTT" />
<ora:sqlplus silent="true" logon="${db.userid}" failonerror="yes">
 <![CDATA[
    define myusername = '${db.username}'
    select * from all_users where username='&myusername';
 ]]>
</ora:sqlplus>

Ant will replace all ${} style constructions with the given value of the property. But this works only for text content (SQL scripts) inside the tag (CDATA section) not with SQL script files that are called using the start attribute. You have to call the SQL file from within the CDATA section to workaround this drawback:

<property name="db.username" value="SCOTT" />
<ora:sqlplus silent="true" logon="${db.userid}" failonerror="yes">
 <![CDATA[
    define myusername = '${db.username}'
    @mysqlfile.sql
 ]]>
</ora:sqlplus>

I have extended Incanto’s <sqlplus> task to simplify this. It is now possible to include a <propertyset> element within the <sqlplus> task.

<property name="db.username" value="SCOTT" />
<ora:sqlplus silent="true" logon="${db.userid}" failonerror="yes">
  <propertyset>
    <propertyref prefix="db"/>
  </propertyset>
  <![CDATA[       
    select * from all_users where username='&db_username';
  ]]>
</ora:sqlplus>

All properties included by the <propertyset> element now can be used inside the SQL script. The <sqlplus> Task is passing these as defined variables into SQL*Plus. But note: since SQL*Plus has some rigid naming conventions for variable names, the <sqlplus> Task converts all dot (.) characters to underlines. Thus in the prior example I reference the property db.username by db_username.

---

Possibly related posts: (automatically generated)

• Uploading files into Oracle with Ant
• Groovy Ant Secrets
• Create a properties frile for Java web app
• Get your ANT together with Subversion and SQL.