Re: SQL*Loader Through Pipes

In article <3B54B4D1.5DBC6EA0_at_attws.com>, "Daniel says...
>
>Has anyone been successful running SQL*Loader via DBMS_PIPES or any
>other means.
>
>Thanks,
>
>Daniel A. Morgan
>

DBMS_PIPE is simply an inter-session communication device, it is not inherently capable of running a program at all. You can write a separate process running outside of the data that listens on a pipe and does whatever you want it to do (run sqlldr for example).

If you are looking for ways to get PLSQL to do a "host" command then some ideas are:

o UTL_HTTP is an easy way if a web server resides on the machine you wish to do the host command. Simply wrap whatever processes you want to make runnable with a cgi-bin script of some sort and use UTL_HTTP.REQUEST to run them.

o use an external procedure written in C. http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:341817437103 is an example of what an extproc routine might look like (does not do host commands, just an example)

o use a java stored procedure
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:952229840241

If you have but a moderate amount of data to load, a couple of thousand or less records, PLSQL by itself may very well be the path to go: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:464420312302

In 9i, an interesting new feature is the ability to attach an external table using a SQLLDR driver. You can then use SELECT on it so loading becomes as easy as "insert into t select * from your_file".

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp