oracle sqlldr question

[Edgar_8]

Hi,

We are currently loading CSV flat files data to an oracle 9i DB table via sqlldr using the direct parallel method.This
is done using a unix script included in cron. What we want to do is create an additional table that stores
a record of all the flat files loaded, does anyone know if/how to do this within sqlldr or is there a more
optimal way? Please advise

Thanks in advance!

[Jean-Luc Oudart]

You could analyze the sqlldr log file and output filename, nbrecords, .. (in a delimited way) into an overall log file and use sqlldr load (append ?) into your new table.
A bit of scripting is required, but this should not be that difficult.

Regards,
Jean-Luc

[Edgar_8]

Hi JL,

Could you help out with the source code for what you suggested?

Thanks in advance!

[Graham Cameron_1]

Don't think sqlldr supports this, you'd have to script something up, like querying the log file as previously suggested.

Since you're using 9i you could be using external tables rather than sqlldr, but again I think you are looking at scripting something in pl/sql to do what you want.

-- Graham

[Jean-Luc Oudart]

You can screen all your logfile.
example
#!/bin/sh

logfile=$1
inputfile=$(grep ^"Data File:" $logfile | awk '{print $3;}')
nbloaded=$(grep "successfully loaded" $logfile | awk '{print $1;}')

echo table "|" $inputfile "|" $nbloaded

========
replace table with your tablename. (I usually have it in the log file name).
My example is based on a simple sqlldr (one table only , no condition) but it gives an idea of what you can build.

Regards
Jean-Luc

[Edgar_8]

Hi,

Thanks for the feedback! How would you propose I script the PL/SQL code?

Thanks in advance!

[Brian Crabtree]

Edgar,

Do you want information from the logfiles, or just a flatfile?

In a unix shell script, you can just do the following:

sqlplus user/pass <> /dev/null
insert into loadtable values (sysdate,$LOADFILE);
commit;
exit;
!

You could get more advanced than this, but this would just be a date that it was inserted, and the filename (assuming that LOADFILE is the internal name for the file). If you want to load extra data (recommended) then follow Jean-Luc's example to get the information out of the log file, and load it in as extra fields.

Thanks,

Brian

[twang]

Hi,

If I understand your problem correctly, you need to create a control file(loadcsd.ctl) to describe the format of the record as below:
-----------
load data
infile '/u01/app/oracle/local/tmp/loadcsd.dat'
append
into table CSD_tab
fields terminated by ','
(mon_date date(24) 'Dy Mon DD HH24:MI:SS YYYY', db_name, col1, col2)
-----------

Another cron script run to load the above data into the CSD_tab:
-----------
datfile=$TMPDIR/loadcsd.dat
#
if test -s $datfile
then
# Load data to ORACLE database
$ORACLE_HOME/bin/sqlldr $ORAUSER/$ORAPASS@$ORACLE_SID control=$TMPDIR/loadcsd.ctl LOG=$TMPDIR/loadcsd.log BAD=$TMPDIR/loadcsd.bad
fi
-----------

Hope this can help.
Good Luck
twang