loading file into oracle

[antman03]

Hi all,

Doing maintenance on the thread search page so I don´t know if this has been asked before, so here goes.

I want to load up a semi-colon delimited file into an oracle table.

Whats the best way of doing it? I haven´t doen this before but of the top of my head there are a couple of options.

I think that I could either write a script to format the file into an oracle export file and then call a PL/SQL script on the DB to load it (plus do some other stuff too)

Write a script to parse each line of the file into variables and then do an insert, (would this involve opening a connection everytime I do an insert?)

Some other way.

If this has been asked before can someone give me a ref to the post otherwise I´m all ears

Thanks

Anthony

 

[vgersh99]

mostly likely you'll need to look into 'sqlldr' and its control file definitions.

A better forum for that would an Oracle related one.

vlad
+----------------------------+
| #include<disclaimer.h> |
+----------------------------+

 

[antman03]

OK I´ve worked out a way to load the file into oracle, but now another question.

I´ve got data in a string in the following format

X = &quot;aaaaa|aaaaaa|bbbbbbb|cccccc||d|d|e&quot;

etc. Although the number of fields are constant the field lengths are variable so I can´t extract data using

print {$X:0:5} etc.

How can I search the variable for the separators &quot;|&quot; and assign that value into another variable.

Looking at that I realise its a really easy question, but I´m not a unix script kinda guy, help please

Anthony

 

[PHV]

awk -F'|'

Hope This Help
PH.

 

[Ygor]

This sets $1=aaaaa, $2=aaaaaa, $3=bbbbbbb, $4=cccccc, etc....

IFS=\|
set $X
unset IFS

 

[IMAUser]

The easiest way to load a datafile is to use sqlldr. It can load fixed length and delimited files. The command line would be
$ sqlldr username/password data=datafile_name control=control_filename log=log_filename

control_filename is the file where in you will have your record layout. So it may read something like

-- Control File --
Load Data
append into table tablename
fields terminated by &quot;|&quot; trailing nullcols
( filed_name_one
, field_name_2
, field_name_3
.....
)

That should do it.