Executing SQLLDR from within a PL-SQL procedure

Hello and thanks in advance. I am running Oracle 11gR2 and want to try to execute an sql loader to insert data into an existing table. I am attempting this via Java stored proc in the database that will perform commands on the OS. The problem I am having is that I cannot seem to get the call to invoke SQLLDR.EXE to work - error I have is: sqlldr not found (NOTE that lookup with PATH isnt done due to the Oracle executable being SETUID)

----------------------Sample Code------------------------------------------

declare l_ldr varchar2(1000) := '/u01/app/oracle/product/db/11.2.0.4/bin/sqlldr.exe control=C:\ad\controlfile.ctl, log=load.log, bad=load.bad,data=C:\somefile.txt'; l_env varchar2(1000) := 'PATH=/bin:/u01/oracle/db/11.2.0.4/jdk/bin;'; l_out varchar2(5000); l_ret varchar2(5000); begin dbms_output.put_line(l_ldr); MSO_Java.dbcmd('sqlldr.exe', l_ldr, l_env, 'Y', l_out, l_ret); dbms_output.put_line(l_ret); dbms_output.put_line(l_out); end; 

--------------------------END CODE SAMPLE--------------------

Appreciate ANY help here. I know this can be done.....

1 Answer

SQLLDR doesn't have the option to be executed in a PL/SQL, but you can simulate a SQLLDR with PL/SQL.

Yeap this is posible, I created a stored procedure that works fine just with REGEXP, now the documentation is in spanish but as soon that I can I will translate this.

Here is the link

Performence? Yeah I now that simulate a SQLLDR will be affect the performance in the database but NO, is this the reason that I used REGEXP. That works fine for me loading masive data.

Another thing is that this PL/SQL gives you the opportunity to transform the data. You can use a ETL for that but sometimes that is not possible :(.

THAT IS EASY TO UNDERSTAND :D!