unix script to export data from csv file to oracle database

Shell Programming and Scripting
1

Hello people,

Need favour. The problem I have is that, I need to develop a unix shell script that performs recurring exports of data from a csv file to an oracle database. Basically, the csv file contains just the first name and last name will be dumped to an Unix server. The data from these files then need to be exported to an oracle database.

I wonder if anyone has done anything similar to this. Does anyone have a shell script that I can borrow? Any help will be appreciated. Thanks.

Vina

2

I guess you want to import data into Oracle [not to 'export data to Oracle].
If that's the case you might want to look into Oracle's sqlldr - that's the data loader.
Here's a sample usage:

$ORACLE_HOME/bin/sqlldr userid=userName/userPasswd control=/path/to/controlFile log=/path/to/logFile data=/path/to/dataFile
3

I use sqlldr on a monthly basis as follows:

  1. Create a control file - filename.ctl
  2. Create an executable script - load_file (or whatever you want to call it)
  3. Make sure the file you want to load does not change name or type, mine is just called start.csv and in comma seperated format. Write some error checking code for this.

##filename.ctl##
load data
infile start.csv
into table contact append
fields terminated by ',' optionally enclosed by '"'
(
URN,
TITLE,
FIRST_NAME,
OTHER_NAME,
LAST_NAME
)

##load_file##
sqlldr scott/tiger control=filename.ctl

##start.csv - first record example##

"0061031","Mr","B","A","Williams"

load_file will look at the control file and import the values from start.csv as required. If you sure the file is being placed in a directory it is worth while checking whether it exists before trying to import it into oracle else abort.

sqlldr does create a filename.log in the directory where you specified unless you specify a path as shown with the example by vgersh99.

Pretty basic but with a cron job should be able to get it working easily.

Hope this helps.

4

Thanks to you both DirkLottering and vgersh99. I'll try it. If i want to make it as recurring process, can I do it by control file ?

I'm new to unix so bear with me if its silly question.

thanks again.