Re: SQL Loader/Multiple Inserts

You could handle this within SQL*Loader if you had the phone number list represented by a varray or nested table column within the main table as oracle provides some constructs
to cope with these situations.
Take into consideration that the format of your input file matters, I mean, it's easier if you have a counter field indicating how many addresses are provided or you know in advance they are a fixed number. If you have just a delimited list, then it is strongly recommended they are either at the end of the record, or the last address must be delimited by a different character (i.e. a comma separated list terminated by a semicolon).

I see however that your approach is purely relational, so your are left with fewer options, the staging table you suggested is indeed a classic solution for this kind of processing.

Since generally speaking a staging table is always advisable when importing external data, I dare to suggest to create this temporary table using the collection column and then let the "checkout" procedure unnest the collection and insert the results into the final table(s). Collection unnesting can be achieved also by means of views.

If I remember well there should be some example of loading collections in the Utilities manual.

Bye,
Flavio Received on Thu Jul 24 2003 - 01:24:37 CEST