Sqlloader: All database in one single plain text file

[BadMan!]

Hi all,

I'm trying to use sqlloader to import a database with multiple
different tables which has been exported to one single plain text
file. No fixed lenght. Let's say database.txt is something like

fruits,orange,orange
fruits,banana,yellow
fruits,pear,green
users,John Doe,25,34,26th upper street,0, 12-2-2003
users,Fred Flinstone, 24,5,14th Downing Lane,1,1-7-2002

¿Is it possible to try an approach based on WHEN statements? I mean:

LOAD DATA
INFILE database.txt
FIELDS TERMINATED BY ','
WHEN (first field is 'fruits')
(name, color)
WHEN (first field is 'users')
(name, age, visits, address, active, since)

I only whant to know if it is possible or not, but will wellcome
advice in ctl file sintax.

Thanks in advance and best regards

[Ralf]

Hallo, BadMan,

look there to find the answer:

http://asktom.oracle.com/pls/ask/f?p...:3516903603571

best regards

Ralf
--
lo****@engineer.com (BadMan!) wrote in message news:<8e**************************@posting.google. com>...

Hi all,

I'm trying to use sqlloader to import a database with multiple
different tables which has been exported to one single plain text
file. No fixed lenght. Let's say database.txt is something like

fruits,orange,orange
fruits,banana,yellow
fruits,pear,green
users,John Doe,25,34,26th upper street,0, 12-2-2003
users,Fred Flinstone, 24,5,14th Downing Lane,1,1-7-2002

¿Is it possible to try an approach based on WHEN statements? I mean:

LOAD DATA
INFILE database.txt
FIELDS TERMINATED BY ','
WHEN (first field is 'fruits')
(name, color)
WHEN (first field is 'users')
(name, age, visits, address, active, since)

I only whant to know if it is possible or not, but will wellcome
advice in ctl file sintax.

Thanks in advance and best regards

[Mark D Powell]

lo****@engineer.com (BadMan!) wrote in message news:<8e**************************@posting.google. com>...

Hi all,

I'm trying to use sqlloader to import a database with multiple
different tables which has been exported to one single plain text
file. No fixed lenght. Let's say database.txt is something like

fruits,orange,orange
fruits,banana,yellow
fruits,pear,green
users,John Doe,25,34,26th upper street,0, 12-2-2003
users,Fred Flinstone, 24,5,14th Downing Lane,1,1-7-2002

¿Is it possible to try an approach based on WHEN statements? I mean:

LOAD DATA
INFILE database.txt
FIELDS TERMINATED BY ','
WHEN (first field is 'fruits')
(name, color)
WHEN (first field is 'users')
(name, age, visits, address, active, since)

I only whant to know if it is possible or not, but will wellcome
advice in ctl file sintax.

Thanks in advance and best regards

From looking at the notes and example in the 9.2 Utilities manual it
does not appear that sqlldr is really designed to load different sets
of data into multiple tables. The only expample takes portions of the
same input and places it into different tables, but the input data
format does not change.

It should however be easy to write a C program or Shell script to
split the input file into one file per table based on the first
delimited parameter being the table name: fruits.dat, users.dat,
etc.....

Perhaps a true sqlldr expert will see your post and have time to
respond if there is any practical way to feed multiple layouts to
multiple tables in a single job.

HTH -- Mark D Powell --

[BadMan!]

db*********@spamgourmet.com (Ralf) wrote in message news:<51**************************@posting.google. com>...

Hallo, BadMan,

look there to find the answer:

http://asktom.oracle.com/pls/ask/f?p...:3516903603571

Darn close to what I'm trying! Thank you very much endeed!

As I suspected, sqlloader doesn't seem designed for such task.

Best regards.

[BadMan!]

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...

From looking at the notes and example in the 9.2 Utilities manual it
does not appear that sqlldr is really designed to load different sets
of data into multiple tables. The only expample takes portions of the
same input and places it into different tables, but the input data
format does not change.
Yap.
It should however be easy to write a C program or Shell script to
split the input file into one file per table based on the first
delimited parameter being the table name: fruits.dat, users.dat,
etc.....
awk is the tool.

But I wanted to know if there was a way to use Oracle-native
platform-independent tools...

Perhaps a true sqlldr expert will see your post and have time to
respond if there is any practical way to feed multiple layouts to
multiple tables in a single job.

Thank you anyway!

Best regards from spain.