Re: Calling SQL*Loader from a Procedure

flyinghigh1962_at_yahoo.com (Clyde) 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

SQL*Loader is a good tool but there are also other very good alternatives. I have found the presence of java in post oracle 8.1.x versions to be very, very helpfull. Most of the OS related stuff e.g. running batch files, shell scripts etc is now a breeze (Providing you are familiar with java) then in previous versions.

I have found java to be an excellent alternative for this job. I have incorporated this solution in some of the projects I have worked on where regular data loads were expected from a flat file. Although, UTL_FILE package is built in, it lacks some of the functionality e.g. scanning for files in a directory, deleting/renaming files etc (Don't know if the functionality changed in 9i or is same as 8.1.x). Using java you can scan for new files, read delimited flat files, rename already processed files etc and you can scrub data and enforce business rules directly from PLSql. All of this from within Oracle.

You can read Oracle documentation or visit Metalink for some good docs on how to create and use java stored procedures/classes.

HTH
//Rauf Sarwar Received on Sun Jun 23 2002 - 23:28:51 CEST