Uploading files into Oracle with Ant

March 5, 2009

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.