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.
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!
