Re: SQL*Loader - How do I use a field from a source file that has no column in the table?

Don wrote:
> I'm using SQL*Loader to load a table and I have the following
> situation.
> The table is as follows:
> -------------------------------
> pk_id Primary Key
> fk_id Foreign Key
> code VARCHAR2(10)
> ------------------------------
> And the source file is as follows:
> val1,val2,theCode
>
> The value for pk_id is entered with a trigger but fk_id is a foreign
> key that I must lookup in another table. I have a stored function
> that will look up this value given val1 and val2 from the source file.
> How do I do this?
>
> I have tried the following in the control file:
> val1 FILLER CHARACTER,
> fk_id getID(:val1,fk_id),
> code CHARACTER
>
> But this fails because val1 is not bound. Is i remove the : from val1
> I get an error that the column does not exists. I have a similar load
> in which the fk_id uses a function that only takes 1 argument. This
> works fine.
>
> How do I use a field from the source file that has no corresponding
> column in the table.
>
> Thanks,
> Don
> dchamber_at_mindspring.com

This doesn't really answer your question, but this is how I'd solve the problem. I always disable constraints when I'm using SQL*Loader, so I'd disable the foreign key constraint, load the table with SQL*Loader, then populate the foreign key column with SQL and re-enable the foreign key constraint.

~bob Received on Wed Sep 05 2001 - 21:14:37 CDT