We help IT Professionals succeed at work.

Call sql loader in stored procedure

chandukb
chandukb asked
on
Medium Priority
11,741 Views
Last Modified: 2008-03-10
Hi,

I have this script on our unix box :

Sqlldr80 vend_comp/vend_comp@vend_comp_aix_main control=vend_comp_load.ctl


Basically, this script load some data files into oracle tables.  

I want to run this script from an Oracle PL/SQL Stored procedure, is that posibble?

If yes, How do I call this script from stored procedure.


Thanks
Chandu
Comment
Watch Question

Commented:
One possible solution is by writing a PL/SQL external procedure that can call a C routine, which at its time uses the system command, like:

system("Sqlldr80 vend_comp/vend_comp@vend_comp_aix_main control=vend_comp_load.ctl");

There are various way to have OS calls in both Unix and Windows.  Please check!

Because Unix provides forking and threading it is possible that this solution is more workable in Unix than it is in Windows.

However, my experience was that debugging a C or C++ dll with an os system call is a real pain in the neck. I heard that from Microsoft that debugging dlls is meant to be that way. I did this with Visual C++, and I encountered some discrepancies with the dll entry points, but I finally got it to work.

If you are using  gnu c/c++, for example, under Unix, the mechanism is the same, and most likely it will be easier to debug.  The PL/SQL Reference contains an entire chapter on External Procedures.

Good luck.




Commented:
Also you could use nohup in your statement to call a shell when using the "system()" function in your C external procedure.  Using "nohup" in the system call
- system("nohup loadshell.ksh"); - and utilizing a shell for this command will make it more stable and prevent any hanging or breaking in the process of OS forking.  On ther other hand, you need to create a library and have an IPC listener that reads it, via the extproc Net8 configured service PLSExtProc. Your library directory should be readable by defining it in the utl_file_dir init.ora parameter.


Your tnsnames.ora file should have section like this.

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

and your listener should look like this:

SID_LIST_ADN =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ADN)
      (ORACLE_HOME = D:\Oracle\Ora81)
      (SID_NAME = ADN)
    )
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Oracle\Ora81)
    )
  )

Author

Commented:
I have no knowledge in C/C++.  I want to be able to run the script from my visual basic application, that is the reason I want to call the script in my oracle stored procedure.


Chandu

Author

Commented:
I found this on the net:

http://www.orafaq.com/msgboard/serverutil/messages/2015.htm


But, our oracle is on Unix box, and I do not know what is
crtdll.dll in the above linek,  Any ideas/suggestions.

Chandu
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
PL\SQL does not support "host" calls by default.  So you cannot simply call an O/S command from PL\SQL.  There are three workarounds:

1. external procedures (as noriegaa mentioned)
2. daemon.exe (see the PL\SQL doumentation for dbms_pipe for an example)
3. Java.

I've used "daemon.exe" for years with NT, but it also works well with Unix.

Java stored procedures can be called from PL\SQL, and they can do at least some O/S tasks, so it may be possible with Java.
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
Hmmm...Though past experience would tell me that Mark generally knows what he is talking about, I'm a bit surprised by the statement that pl/sql does not support host calls.

I have used HOST statements for running SQLLDR from within a PL/SQL block in an Oracle Form.  Similarly, one of the help examples on the host built-in in Forms refers to calling host in a pl/sql block (see below).  Perhaps the difference is that these are executing the host command on the client and not on the server...

The format could be as follows:
HOST(sqlldr user/passwd@database control='your path to your control file goes here' LOG='your path to your error log goes here' ERRORS=500);

Depending on the loader executable, the call may be either to SQLLDR or to SQLLDR80 or something similar.

You will need to specify the whole path to the control file in addition to the file name.

Please note however that if you are putting this in a server side procedure, then SQLLDR must be accessible on the server.
Regards,

JT
MI and Automation
CERTIFIED EXPERT
Commented:
Oh yeah...and the ORacle example...

** built-in:  HOST
** Example:   Execute an operating system command in a
**            subprocess or subshell. Uses the
**            'Get_Connect_Info' procedure from the
**            GET_APPLICATION_PROPERTY example.
*/
PROCEDURE Mail_Warning( send_to VARCHAR2) IS
  the_username VARCHAR2(40);
  the_password VARCHAR2(40);
  the_connect  VARCHAR2(40);
  the_command  VARCHAR2(2000);
BEGIN
  /*
  ** Get Username, Password, Connect information
  */
  Get_Connect_Info(the_username,the_password,the_connect);
  /*
  ** Concatenate together the static text and values of
  ** local variables to prepare the operating system command
  ** string.
  */
  the_command := 'orasend '||
      ' to='||send_to||
      ' std_warn.txt '||
      ' subject="## LATE PAYMENT ##"'||
      ' user='||the_username||
      ' password='||the_password||
      ' connect='||the_connect;
 
  Message('Sending Message...', NO_ACKNOWLEDGE);
  Synchronize;
  /*
  ** Execute the command string as an O/S command The
  ** NO_SCREEN option tells forms not to clear the screen
  ** while we do our work at the O/S level "silently".
  */
  Host( the_command, NO_SCREEN );
  /*
  ** Check whether the command succeeded or not
  */
  IF NOT Form_Success THEN
    Message('Error -- Message not sent.');
  ELSE
    Message('Message Sent.');
  END IF;
END;

Commented:
You mentioned above about what is crtdll.dll.
On a unix platform there should be a library crt1.o
or crt0.o (i think somewhere in /lib or /usr/lib).
You could use this instead of the crtdll.dll in the
link that you specified above.
Hope this helps.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
To jtrifts:

You are correct, at least on the client in OracleForms, PL\SQL does support "host" calls.  On the database server though, the database PL\SQL engine *does not* support "host" calls.

Since the question states "on our unix box" I interpreted that to mean this question was about PL\SQL on the server.  That is why I listed the three work-arounds that I am aware of for host commands through PL/SQL.
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
Thanks for your comment Markgeer.  I can now rest easily knowing that my earlier *surprise* was in fact unfounded! (I should never have doubted you)

:O)

Regards,

JT

PS As an aside, why is this so?  Is it a security issue? Is this for earlier versions only or all versions of Oracle?

Commented:
My solution is viable.  All you need to research is how to write a very small C function, and tie it with the pl/sql external procedure/function.  N.B. I neglected the & at the end of the nohup command line in your system() os call.  I think that the only difficult portion of this work, and it is not difficult at all, is to troubleshoot the extproc-based ipc listener process.  Everything else is logic and rather trivial.  There is more testing involved than actual coding.  The procedure will run from the server and it should use your library and utl_file_dir info (recommended) to access your loader data as well, i.e., no process will encounter any problems accessing subdirectories when process forking takes place.  

Best wishes.

Anthony

Author

Commented:
I decided not to call sql loader at this time due to project dead line, but I got a pretty good idea and I will play with it later on.

thanks to all for your help.

chandu