Re: Calling SQL*Loader from a Procedure

"Clyde" <flyinghigh1962_at_yahoo.com> wrote in message news:bf477768.0206212303.6dbe8bb6_at_posting.google.com...
> I'm creating an application(9i on W2000) where the user will need the
> ability to hit a button and load data from a .csv file in a given
> directory into the database. There would be a new file on a daily
> basis the user would need to load. My original assumption was to use
> SQL*Loader to move the data, but I'm having difficulty finding
> examples of how it can be called from a procedure. I'm familiar with
> SQL*Loader, and writing packages and procedures, but haven't had the
> need to put the two together until now. I'd rather not get into Pro*C
> if I can help it. Is there a simple way to call SQL*Loader from a
> procedure such as using DBMS_JOB, or some other Oracle supplied
> package? I believe all I would need to do would be to send a command,
> or a string, to the DOS prompt to kick off the SQL*Loader.
>
> If I can't do this, what are my other options to load this data? Each
> of these files will probably be about 6M-10M.
>
> All ideas, suggestions, and direction greatly appreciated!
>
> Clyde Reed

Can it be done?
[Quoted] [Quoted] 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.
[Quoted] 1 you set up a .cmd or .bat file that is processed by the Win2k at command [Quoted] or whatever is has replaced it. At is basically a batch facility. You keep [Quoted] 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.
[Quoted] 2 Write a vbscript program calling the VBA functions in Excel/Access to open [Quoted] a .csv file and dump it in Oracle using ODBC.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address