Re: SQLLdr functionality - scripting? (newbie question)

"Tim Smith" <timasmith_at_hotmail.com> wrote in message news:a7234bb1.0309181514.64af86ac_at_posting.google.com...
> Hi,
>
> If you have a delimeted text file which is to be uploaded into 2
> tables, one a parent table and the other a child table - how is this
> handled when the primary key is generated from a sequence? Also in
> general is there a PL/SQL like scripting language associated with
> Sequel Loader to special logic - like lookup up database values in
> order to populate a column?
>
> thanks!
>
> Tim

First question: if you are loading both tables using the same input record, then you can manage to spawn the sequence numbers using SQL Loader's keyword SEQUENCE. Note that the sequence number is unique per each input record, so even if you have multiple destinations the number is the same (unless you specify some special options for this keyword). You can find out more on this topic in the Utilities manual, go to section dedicated to SQL Loader and
look up SEQUENCE. On the other hand if data comes from different input records, then you must have some rule in place to keep together parent and child, so you must probably deal with the problem in a second phase.

Second question:
The special logic is PL/SQL functions. Write your own function and then invoke it from within SQL Loader, you can specify arbitrary expressions within double quotes as last parameter inside the field specifier, i.e.

...
MY_COLUMN CHAR "MY_FUNCTION(:MY_COLUMN)" ...

Note that when you use such feature you must execute SQL Loader in conventional mode, not direct path.
The utilities manual can be found at http://tahiti.oracle.com

Bye,
Flavio Received on Sat Sep 20 2003 - 14:36:09 CEST