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.

Deprecation within Database Schemas

Java developers already know about deprecating API’s – the ability to mark specific classes or methods as “deprecated” thus informing API users that they should stop referencing those parts, because they might be removed in future releases. Introducing such a transition period allows code refactoring being less painful (mainly for the public API parts) and preserve backward compatibility.

Along with the rise of new approaches to more agile database techniques, came the idea of using similar practices also in DB development. That means for example, if you want to remove a column then you don’t drop it immediately but mark the column as deprecated. After a transition period of some weeks or months, the column will be dropped. This strategy is of particular interest to database refactoring, because usually database schemas are highly coupled to a wide variety of things. And unlike Java API’s where we can distinguish between protected and public API parts, items within a database schema are potentially always public.

What to deprecate?

The following database artifacts are items that might be deprecated:

• Tables
• Views
• Single Columns
• Stored Procedures, Functions, Sequences

When to deprecate database items?

• Item will be dropped in future
• Is inefficient or insecure to use

How to deprecate?

A deprecation mechanism should fulfill the following requirements

• Deprecation markers should be attached to their items in a manner that they are preserved even if the items will be moved (import/export etc)
• Markers should refer to an explanation why the item has been deprecated and suggest what to use instead
• Throw warning messages when deprecated items are used

Techniques to deprecate database items

In case of Java, the deprecation is carried out through @deprecation comments within the source code. The Java Compiler throws warning messages when it finds references to deprecated items. Obviously within a database we cannot issue compile warnings. The only way to inform about calls to deprecated items is at runtime. That means every time a deprecated table, column, view or so is accessed, a warning message should be written to a log table. But how can we know when those items are accessed?

Oracle databases are offering the following options:

1) Triggers: It’s the standard way to attach DML monitoring to database tables. But triggers cannot monitor on SELECT’s and they are not working on Stored Procedures, Sequences and things of this nature.

2) Database Auditing: With Oracle’s Regular Auditing one can monitor also the use of Views, Functions and Sequences plus one can even log the SQL statement that causes the audit event. But to monitor table access on column-level, Fine-Grained Auditing (FGA) is needed. FGA is only available with Oracle Enterprise Edition.

Issuing deprecation warnings

Another problem is about the deprecation warning messages content itself. The logged messages should help developers and admins to figure out from where deprecated items are accessed. That means at least the program name and SQL statement should be logged. Unfortunately Oracle doesn’t offer something like a stack trace (as Java).

Conclusion

Wouldn’t it be nice if databases offered a standard mechanism to deprecate all the variety of items within a database? I think that will be a huge help towards achieving a more agile way of database design.

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.

Jump-start with Oracle’s Instant Client and Incanto

I just did a test-drive with Oracle’s new free-of-charge Instant Client to see how it fit’s with the Incanto <sqlplus> Ant task. I was pleasantly surprised how well and simple it works. The Oracle Instant Client has a very small footprint and can be used with minimal installation efforts. Therefore it’s particular valuable for those cases where you don’t want or can’t install a full Oracle Client.

To use the Oracle Instant Client with Incanto, you have to consider the following:

1) Deploy the Oracle Instant Client and the SQL*Plus package into any directory.

2) Make sure this directory is on the path when running Ant.

N.b.: If you already have another Oracle Client installed, then you won’t be able to add the Instant Client directory to the PATH variable. In this case you can either set the Instant Client directory as the working directory for your Ant build or change the PATH variable within Ant’s launch script.

3) Set the TNS_ADMIN environment variable or registry setting to the full path of the tnsnames.ora file. This way you can use alias names specified in the tnsnames.ora.

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.