Re: invoking SQLLDR in ASP

brinda <member45418_at_dbforums.com> wrote

> We need to invoke SQLLDR from our ASP code in the server to load data
> into Oracle server from flat file.
>
> We have created a dll in VB to run the sqlldr using Shell cmd. When we
> call the dll from ASP, the sqlldr is not getting invoked.

Did you use the CreateProcess() and older WinExec()? If the latter, be careful. It in turns call CreateProcess() using defaults that may not work for what you are trying to do. Microsoft also recommends that WinExec() not be used.

> If we do not pass the Control File parameter to SQLLDR in the Dll Code,
> on running the ASP page, we can see the SQLLDR exe in the Task Manager .
>
> Otherwise we are not able to see the exe at all.

Likely because it immediately bombed out?

> Any inputs to this will be highly appreciated.

Do not call SQL*Loader directly. Script a CMD file that sets up a proper environment and does the SQL*Loader thing.

Remember that ASP pages are usually running in a guest account - not in a normal user account. Thus logon as the *same* NT user you use for IIS and run the CMD file script there. Make sure it works.

Also make sure that it can handle two (or more) simultanuous requests (especially when dealing with log filenames as output and data filenames as input). If they are not unique, one web client's request will very likely overwrite the non-unique files from another web client request and trash them.

Lastly, may I offer an alternative that I believe will work a lot better? I do not think it a great idea to fire up SQL*loader in the background like this. You have basically no management and control over that process, nor know the result of that process. Add to that the issues of the guest environment being run in.

Instead, I would rather use an external Oracle table. This table is dynamically SQL*Load'ed when selected from. It is controlled from a normal Oracle session using the standard SELECT FROM TABLE syntax.

If it is not possible to use a single table for the web requests, I would create a PL/SQL package. This package will use dynamic SQL to create a unique table for me (I will give it a session id from ASP to use as the unique tablename). In addition, it will receive the filename to SQL load. All done from ASP as standard Oracle session statements (i.e. calling SQL and PL/SQL).

The only problem is when the file to load sits on the web server and the Oracle db you are querying sit on another server. In that case the easiest would be a (secure) file mapping between the two (i.e. the web server shares the load file dir which is in turned mapped by the Oracle server). There are also more complex solutions to this problem (including submitting the file's content's as a CLOB using PL/SQL web cartridge).

The bottomline to this is no hacks via DLLs to run background processes. Having full control and management over what is SQL*Load'ed directly from a standard ASP connection to Oracle where the contents of that CSV file is accessed via an external Oracle table.

--
Billy