shell - insert timestanp of INFILE into a column from SQLLOADER

Question

I have a requirement as below,

Am calling sqlldr script via shell for the CSV files present in a folder, File name also has Timestamp attached with it.

I need to insert that timestamp into a column of table. Kindly suggest me how i can achieve this.

eg: table: t1(c1 varchar,c2 varchar,c3 timestamp);

control file : load data infile 'file.csv' append into table t1 fields terminated by "|" TRAILING NULLCOLS ( c1, c2)

csv_file : cat file_csv_101010112233.csv

1111|1

2222|2

OUTPUT : select * from t1;

c1 c2 c3

1111 1 101010112233

2222 2 101010112233

Note : I dont want the sys timestamp

Answer 1

I think you will need a shell script wrapper around calling sqlldr. First alter the control file so the timestamp column has a placeholder like:

...
C3 CONSTANT REPLACE_ME,
...

And save it as a template.

The wrapper should back up the original csv file, get the timestamp from the filename, then use something like sed to replace the "REPLACE_ME" text in the template control file with saved timestamp data and save it to a working copy, then call Sqlldr using the working copy.

I was thinking of other ways to do this and came up with one. May not be feasible for your environment but something to keep in mind anyway.

If you can get the data file name into a column (maybe a load_log table for example that would get populated at the start of the load), you could assign it like this by calling a function that returns the name:

C3 "package.function"

More info: SQL*Loader Field List Reference