import a csv file into Oracle DB using SQl loader in Unix environment [message #128941] |
Thu, 21 July 2005 05:55 |
vina
Messages: 3 Registered: May 2005
|
Junior Member |
|
|
Hello people,
I'm a newbie to Unix and SQl loader so help. The problem I got is
An external application generates a csv file overnight everyday and dumps it to an Unix box. The csv file has data such as ID, First Name, Last Name, Address..etc.
i. I need to load the data from the csv file to an Oracle database everyday probably at night (RECURRING PROCESS).
ii. Remove the csv file as soon it has been uploaded.
iii. The repeat the above process everyday.
I know sql loader will be the perfect tool to do this. The trouble is I'm not sure how to create a shell script which will invoke the sql loader and utilise the .ctl file to upload data from the csv file to Oracle DB.
My control file looks like as below.
LOAD DATA
INFILE C:\start.csv
APPEND
INTO TABLE RE.contacts
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
URN,
TITLE,
FIRST_NAME,
OTHER_NAME,
LAST_NAME
)
And my start.csv file looks like as below
"0061031","Mr","B","A","Williams"
Please help me to create (or borrow your script!!)a shell script that runs on Unix enviroment that does the job as required above.
Appreciate any help. Thanks.
|
|
|
Re: import a csv file into Oracle DB using SQl loader in Unix environment [message #128969 is a reply to message #128941] |
Thu, 21 July 2005 07:16 |
tarundua
Messages: 1080 Registered: June 2005 Location: India
|
Senior Member |
|
|
Hi ,
I dnt hav a UNIX box right now but i m trying to help u out, u can check it on a test machine.
--> Create a Unix shell script which contains your command. for instance
$ cat daily_load.sh
sqlldr control=dataload.ctl log=dataload.log userid=user/passwd data=dataload.dat
( save it & change the mode of the file to executable using chmod command)
--> Now use the CRONTAB command for scheduling ur Unix shell script to run daily at whatever time u want . For instance --
crontab command is used for scheduling.
$ crontab cmdfile
This cmdfile should not only contain commands which we wish to get executed but also the details of date and time in a specific format. This format is given below :
Minute Hour DayofMonth MonthofYear DayofWeek Command
Eg :
$ cat > cmdfile
30 10 1 * * echo “work hard on first day of the month”
0 0 17 11 * mail aa2 < confi.letter
{ Explanation : The first one would be used to echo message at 10:30am on first day of every month. The second message would mail the contents of the file confi.letter to the user on 17th November of every year }
A “*” means all possible values.
for eg in ur case--
$cat > cmdfile
30 23 * * * ./daily_load.sh
( it wil run daily night at 11:30 )
--> And then remove the file which has been uploaded in the database.
I think this information would be helpful to sort out ur situation. But b careful first of all try to check it out on a test database.
Regards,
Tarun
|
|
|
|
|