Re: Calling SQL*Loader from a Procedure

On 22 Jun 2002 13:14:14 -0700, flyinghigh1962_at_yahoo.com (Clyde) wrote:

>Thanks for the ideas--gives me something to work with. I'm just kind
>of surprised that there isn't a more elegant/efficient/automatic way
>of calling SQL*Loader from a package. I assume that users importing
>new data into a database is a regular occurence. Have you run across
>this issue, and how did you handle it?
>
Interactively importing new data into a database is not a regular occurrence on the projects I have been on. Importing data into the database on a regular basis is a reasonably normal occupance, usually involving some procedural work to merge the new data into the database.

If I use SQL*Loader, I load the data into a work table where it can be processed. Based on data comparison, data can be either, ignored (unchanged), added (new), updated (modified), or rejected (invalid).

Alternatively, the data is parsed by a program that makes the above determinations and reacts accordingly.

If you noticed a lack of deletes above, they are treated as updates.

/Bill

>Thanks again for your help!
>
>Clyde Reed
>
>
>>
>> Can it be done?
>> By writing an external procedure calling the C system function, yes.
>> Is it desirable to block the client session because of Sql*loader? Not sure.
>> I think you have several alternatives.
>> 1 you set up a .cmd or .bat file that is processed by the Win2k at command
>> or whatever is has replaced it. At is basically a batch facility. You keep
>> the filename of the batch constant, however, a pl/sql procedure in the
>> application, will rewrite the file, using utl_file everyday with a new
>> sql*loader call. Should work like a charm, assuming you can use an O/S
>> authenticated account in your Sql*loader call, otherwise you would have to
>> giveaway the password.
>> 2 Write a vbscript program calling the VBA functions in Excel/Access to open
>> a .csv file and dump it in Oracle using ODBC.
>>
>> Hth
Received on Sun Jun 23 2002 - 01:47:50 CEST