Re: SQL Loader help needed - newbie

Hi.

A way to do what you want is by using a table trigger. But i think that this requires a temporary table, where you will insert the data from the delimited file. Then you must create a trigger after insert on each row for this table, that does the insertion to the table you wanted in the first place, along with the date (sysdate maybe?). I think that this is the quickest way to do this, you could create a SQL*Plus script that creates this temporary table, creates the trigger, calls sql*loader to insert the data to this table and drops the table after the insertion. Just be careful of the data types of your delimited file. From my (little) experience, i 've used this technique with a temporary table that comprises of varchar2 data type columns and during the insert, inside the trigger, all data are cast to the actual data types. Of course in case of a conversion error and because this is inside a trigger, no rollback or commit is available (right?). You must make sure that the delimited file contains data of the appropriate types.

Thanks,
Vasilis

milan vallecha wrote:
>
> I'm using SQL* Loader to load bulk data from a comma delimited file.
> The structure of this delimied file goes like this :
>
> <header info>
> <actual records to be inserted>
>
> header info: comprises of a field containing a date
>
> I need to get this date from the header info of this file and insert
> it as the last field in all the inserts which form the next part of
> the same comma delimited file.
>
> e.g sample contents of the delimited file.
>
> Date : 22-Nov-2002 - forms header Info
>
> abc, def, ghi, 1, 2, 3 - forms actual records to be inserted
> pqr, ghi, tef, 8, 6, 7 -do-
> wxy, lmn, abc, 6, 6, 9 -do-
> .
> .
> .
> what I need is to insert this date : 22-Nov-2002 as the last column in
> all the records.
>
> abc, def, ghi, 1, 2, 3, 22- Nov-2002
> pqr, ghi, tef, 8, 6, 7, 22- Nov-2002
> wxy, lmn, abc, 6, 6, 9, 22- Nov-2002
>
> If SQL Loader can't do this, I'm prepared to take up any work-arounds.
> so pls do mention them too.
>
> TIA
>
> rgds,
> Milan
Received on Fri Nov 22 2002 - 09:51:52 CET