Re: Help with calling SQL Loader from a function

Hmm, upon further testing, it seems that SQL Loader is the source of the problem. If I run the following in a command window, SQL Loader never returns to the prompt:

sqlldr userid=adalij/adalij_at_JHA1 control=c:\temp\testdata.ctl log=c:\temp\data.log errors=100

However, if I remove the '@JHA1' service name from the userid parameter, SQL Loader *does* return properly. In both cases the data is inserted correctly.

I'm not sure why SQL Loader does this, and I've been trying to investigate whether I have missed something in configuring Service Names and Listeners, but it seems that they are configured properly.

Anyone have any suggestions? I would greatly appreciate it!

Thanks,
John

johnadali_at_yahoo.com (John) wrote in message news:<d0d2f703.0205260618.62a0a5c3_at_posting.google.com>...
> Hi,
> I'm trying to call SQL Loader from a function to bulk insert some
> data into an Oracle database, using Windows NT. If I open a command
> window and call the SQL Loader program directly, everything works just
> fine. I have created a function that calls SQL Loader, but when I call
> this function from SQL Worksheet, the data is inserted into the
> database, but the function never returns, stating it is finished. Can
> anyone help me with this problem? I need to have the function return,
> so I know when the data is inserted. Here is the code that I'm using:
>
> connect sys/change_on_install
> grant create any library to scott;
> connect scott/tiger
> create or replace library clib as 'c:\winnt\system32\crtdll.dll';
> /
> CREATE OR REPLACE FUNCTION plsql_system(cmd in varchar2)
> RETURN BINARY_INTEGER AS EXTERNAL LIBRARY clibNAME "system";
> /
>
> plsql_system('sqlldr userid=adalij/adalij_at_JHA1
> control=c:\temp\testdata.ctl log=c:\temp\data.log errors=100');
> /
>
> Please note that I know that the records are being inserted because
> the log file is generated and the log file says it inserted the data
> okay.
>
> Any help here is appreciated. Thanks,
> John
Received on Wed May 29 2002 - 09:24:25 CDT