Hi all...

Im looking to pass the contents of a simple file to Oracle so that it can be stored in a database table.

The best way i can think of to avoid overhead is to loop through the contents of the file and store the data in a bash array. then the array can be passed to SQL Plus where Oracle can loop through the array and write the data away.

Is this a good way of doing it? if it is...does anyone have an exmple of how to pass an array from the bash shell to oracle

eg simple file called name.txt that contains the following..
tom
dick
harry

any help on this would be reatly appreciated?

This can be done by looping through lines in the file and for each line open up an oracle connection to write the data away eg but tis is expensive performance wise.

loop through file contents
"insert into table values variable1, variable2 ect| sqlplus -s scott/tiger

look into Oracle 'sqlldr'

sqlldr is the best choice - vgersh99 is right on top of that.

Otherwise learn about the UTL_FILE package. And learn some PL/SQL.

Sometimes I've used an editor to turn a small flat file into several dozen insert statements, when the data is in columns and my editor supports macros. Turn

John Doe USA
-- into 
insert into mytable values ( 'John', 'Doe', 'USA');
............
commit;

Hi Guys, thanks for the sugesstion but i am already using SQL loader to load the contents of a csv file into Oracle and I can't use UTL FIle as That can only be used to manipulate files located on the same server as the database. I am working with files on the applications server which is different to the database server in our environment.

The file contents I want to load into Oracle is actually the LOG file produced by SQL Loader and this file is not of any particulat format hence the use of arrays to pass it to Oracle in 1 go.

It would be good to have an example of passing a bash array into oracle as reference... pretty pleaseeeee

Dosnt anyone have an example of how to pass a Bash array to to Oracle??