Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle sqlldr question

Edgar_8
Regular Advisor

oracle sqlldr question

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!
7 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: oracle sqlldr question

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
fiat lux
Edgar_8
Regular Advisor

Re: oracle sqlldr question

Hi JL,

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

Thanks in advance!
Graham Cameron_1
Honored Contributor

Re: oracle sqlldr question

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Jean-Luc Oudart
Honored Contributor

Re: oracle sqlldr question

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
fiat lux
Edgar_8
Regular Advisor

Re: oracle sqlldr question

Hi,

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

Thanks in advance!
Brian Crabtree
Honored Contributor

Re: oracle sqlldr question

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
Honored Contributor

Re: oracle sqlldr question

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