Uploading files into Oracle with Ant

Frequently I come across the need to upload files into Oracle as LOB’s. For instance, I let Ant install some SQL scripts and want to upload the resulting log files as a kind of installation protocol into the DB. I’ve found the easiest way to do this is using the SQL*Loader tool (for which I have the appropriate Ant Task) directly from Ant.

Here is how it works:

<echo file="uploadlog.ctl">
    <![CDATA[
load data
infile *
replace
into table MYLOBTABLE
fields terminated by ','
(
lobid char(100),
lob_file FILLER char,
clobdata LOBFILE(lob_file) TERMINATED BY EOF
)
begindata
transferlog,${sqllog.file}]]>
</echo>
<ora:sqlldr logon="user/pass@oradb">
	control = uploadlog.ctl
</ora:sqlldr>

First I write a temporary CTL file (with the <echo> task) to specify that I want to upload the file behind the ${sqllog.file} property, afterwards I execute this CTL file with the SQL*Loader.

And jeez, I can even upload whole filesets (directories and sub directories) when I use the <for> loop task.

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.

Ant, Groovy and the Database

When you are using Ant to cope with databases then you probably might feel the need to read Ant properties from the database with some lines of SQL. So Ant scripts can be controlled by values from the database. For instance one may query a version table to decide whether a deployment script can succeed or fail.

With Groovy and it’s SQL module it’s quite simple to do this. The following example shows how to query all parameters from Oracle’s NLS_DATABASE_PARAMETERS table and use them as properties within an Ant script.