Home » RDBMS Server » Server Utilities » SQL*Loader input file directory and data file dynamic - ?
SQL*Loader input file directory and data file dynamic - ? [message #74124] Thu, 16 September 2004 05:25 Go to next message
petzi
Messages: 5
Registered: September 2004
Junior Member
hi,

sorry, but I'm an Oracle Newbie...the problem is.

every hour a datafile with about 60 000 is generated from an external application, and this file should be loaded in an OracleDB  with a cronjob.

But the admin can determine the directory of the input file and of course the input data file name isn't fix anyway.

1. the import directory is set in a config table in the database -

i tried to create a select statement - but how do i use this in my control file??? no idea...

data_dir=???

 

2. the next problem: every day a table is created named e.g. for August 25 --DATA_20040825 - - and all files that are generated at this day, are inserted in this table (i have to check errors etc..) . But I don't know the exact input file name either.

 

I don't know how the control file should look like - no idea...

can anybody help me??

Thx..

this should be a Unix Script...
Re: SQL*Loader input file directory and data file dynamic - ? [message #74127 is a reply to message #74124] Thu, 16 September 2004 07:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
#!/usr/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/9.2.0
export PATH=$PATH:$ORACLE_HOME/bin
#
#-- let this be your data_dir
#
DATADIR=/home/oracle/scr/ora/dir_for_sqlldr
clear
# idea is to browse through all the files (input files)
# in the directory , pick the name of the files
# pass it as input parameter to the sqlldr.
# sqlldr control file need to be generated like this for every load.
# if multiple files need to be loaded into same table, it can be done
# You can also use a simple perl or sed script to generate a controlfile.
# So all the files in DATADIR is loaded.
# after every load u need to move the file that is loaded to somewhere.
# for your second issue, if the TABLE_NAME and input FILENAME can 
# be standardised(have the same NAME based on some dateformat,
# its going to be a lot easier.
find $DATADIR/* | while read INFILE
do
echo load data                                    > /tmp/temp1.ctl
echo infile "'$INFILE'"                         >> /tmp/temp1.ctl
echo append into table member                 >> /tmp/temp1.ctl
echo fields terminated by ',' trailing nullcols  >> /tmp/temp1.ctl
echo (name,                                     >> /tmp/temp1.ctl
echo address)                                    >> /tmp/temp1.ctl
echo
echo invoking the sql*loader
echo using table $INFILE
sqlldr userid=mag/mag control=/tmp/temp1.ctl
echo "****************************************************************"
# You can now move your used $INFILE to somewhere..
done

----------------------------------------------------------------------

this is a sample session output of above script
anotherfile.dat has NO records in it.

----------------------------------------------------------------------

invoking the sql*loader
using table /home/oracle/scr/ora/dir_for_sqlldr/anotherfile.dat

SQL*Loader: Release 9.2.0.4.0 - Production on Thu Sep 16 11:38:05 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

****************************************************************

invoking the sql*loader
using table /home/oracle/scr/ora/dir_for_sqlldr/member.dat

SQL*Loader: Release 9.2.0.4.0 - Production on Thu Sep 16 11:38:06 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
****************************************************************

Re: SQL*Loader input file directory and data file dynamic - ? [message #156721 is a reply to message #74124] Fri, 27 January 2006 05:48 Go to previous messageGo to next message
info2sudheer
Messages: 1
Registered: January 2006
Location: Hyderabad
Junior Member
hi,

sorry for the distrubance...I have a problem while Using SQL Loader, the problem is.

every hour a datafile with about 10 000 records is generated from an external application, and this file should be loaded in an OracleDB within a scheduled timings. Of course the input data file name isn't fix anyway.

1. the import directory is set in a local directory say C:/Test/ which contains the list of data files

I created one batch file which is having static behaviour, can i create the control file dynamically so that i can send it to the sql loader. How can i solve this, please help me.... 

I don't know how the control file should look like - no idea...

can anybody help me?? All this should be in Windows based ..

Thanks ....

[Updated on: Fri, 27 January 2006 05:54]

Report message to a moderator

Re: SQL*Loader input file directory and data file dynamic - ? [message #156761 is a reply to message #156721] Fri, 27 January 2006 09:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Same concept applies. You need to generate the controlfile dynamically for every load with a new input datafilename.
check here
http://www.orafaq.com/forum/t/26733/0/
Re: SQL*Loader input file directory and data file dynamic - ? [message #229824 is a reply to message #74124] Tue, 10 April 2007 02:26 Go to previous message
matdi
Messages: 2
Registered: January 2007
Junior Member
What is the code if I have fixed length files to load ?
Previous Topic: Creating a new database from a dump file
Next Topic: Data Pump import
Goto Forum:
  


Current Time: Sun Dec 04 20:23:05 CST 2016

Total time taken to generate the page: 0.09968 seconds