OraFAQ Forum: Server Utilities » import a csv file into Oracle DB using SQl loader in Unix environment

Home » RDBMS Server » Server Utilities » import a csv file into Oracle DB using SQl loader in Unix environment

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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

Report message to a moderator

Re: import a csv file into Oracle DB using SQl loader in Unix environment [message #128973 is a reply to message #128941]

Thu, 21 July 2005 07:24

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

vina is your shell script.
that is all you need!.
replace with your values.

oracle@mutation#cat vina
#!/usr/bin/bash
sqlldr userid=mag/mag@magsdatabse control=magscontrolfile.ctl

>>ii. Remove the csv file as soon it has been uploaded.

Will the name of csv file remain same?
why delete? what if the loading process failed?
anyhow, just touch / rm the file or mv to a different name for backup.
Else,
You can change the script so that, every time you give filename as input prior loading.
See here how to do that ( the example talks about storing the filename inside the database. Ignore it).

http://www.orafaq.com/forum/t/12234/0/

>>iii. The repeat the above process everyday.
create a Cronjob in crontab!.
Google /man page for crontab for examples.
these are typical entries ( from my PC).

oracle@mutation#crontab -l
# minute
# |  hour
# |  |day of month
# |  | | month
# |  | | | day of week 0=sunday
# |  | | | |
#----------------------------------------------------------------------------#
#runs every 30 minute, from morning 6 to evening 10 all days
0,30 6-22 * * * /home/oracle/scr/app/dbcheck > /tmp/dbheck.log 2>&1 
#runs every 30 minute from 15th minute all days
15,45 6-22 * * * /home/oracle/scr/app/inscheck > /tmp/inscheck.log 2>&1
#------------------------------------------------------------------#
#------------ Monitor database growth /health check ---------------#
#runs 8am all days
0 8 * * 0-6 /home/oracle/scr/app/mon_tablespace > /tmp/mon_tablespace 2>&1
#runs 7am on mondays
0 7 * * 1 /home/oracle/scr/app/dbrep >/tmp/dbrep 2>&1
#------------------------------------------------------------------#
#------------ Essbase Extraction : schedule on demand--------------#
#run at 2pm July 18
0 14 18 7 1 /home/oracle/scr/etl/pre_extract_etl803 >/tmp/pre_extract_etl803 2>&1
#run at 4am and 4.05 am July 21
0 4 21 7 4 /home/oracle/scr/etl/pre_extract_etl803 >/tmp/pre_extract_etl803 2>&1
5 4 21 7 4 /home/oracle/scr/etl/extract_etl803 >/tmp/pre_extract_etl803 2>&1

Report message to a moderator

Re: import a csv file into Oracle DB using SQl loader in Unix environment [message #129016 is a reply to message #128973]

Thu, 21 July 2005 09:48

vina
Messages: 3
Registered: May 2005

Junior Member

Hi Tarun..thanks for your input. I'll try it.

Hello Mahesh,

the csv's filename will remain same. Actually what you said is absolutely true. It will be good idea to move the completed csv file to another directory. I''l try it.

Thanks.

Vina

Report message to a moderator

Previous Topic:	my_to_date SQLLDR
Next Topic:	SQL*LOADER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 04:30:02 CDT 2024