Re: SQl*Loader Can and Can't do

>Hi group,
>
>In the absence of an FAQ (??), I would like an expert Oracle user to
>validate the following statements. Your insight is much appreciated.
>
>1 -SQL*Loader can/is installed as part of the client tools. In other words
>you do not need a copy of the server to use the tool.
>

You do need access to an Oracle DB somewhere, but it can run on the client. However, it is usually better to run it on the system where the data file is located. This prevents a LOT of network traffic. If the data file is on box A, and you are running on box B, every byte of the data files gets sent over the network from A to B, then processed, then sent back out to wherever Oracle is running.

>2. SQL*Loader cannot run multiple commands in one script. You can't load
>data from A into table_A then data from B into table_B in the same control
>file (assuming A and B have different fields of different types).
>

You can conditionally insert into a table, and you can insert data (the same row) into multiple tables in the same SQLLOAD command. I don't know if you can can specify multiple input data files though. If the data is in one file, and you can tell when to put a row in which table, you can probably do it. Depends on what you know about the data.

>3. SQL*Loader does not use environment variable. If you need to either
>APPEND or REPLACE you either need to parse and modify the control file or
>have a shell script conditionally run one or the other (assuming two control
>files, one for APPEND, one for REPLACE.)
>

Correct. However, I have a SQLPLUS job that generates the CTL file, then does a HOST to execute it. (I need it dynamic because the data file name is a variable)

>Please reply to the group and if possible to NOSPAMftiburce_at_frameworks.ca.
>(remove NOSPAM)
>
>Thanks a lot. Fabien
>

Try the DIRECT=TRUE option - speeds things up ALOT (but at a price - see the documentation)

Hope this helps.

Dan Hekimian-WIlliams Received on Thu May 13 1999 - 05:08:13 CEST