Accessing Unix Files in PL/SQL

Hi all,

I need to open and process a series of files in a unix directory. Each file contains an xml document that I parse and apply to my database.

I have tried using UTL_FILE but I don't know the name of the file that I wish to open.

Is there a way of performing an 'ls' in my directory from PL/SQL so that I can get the file names and then use UTL_FILE.

OR

Is there a totally different way of solving this problem.

Thanks in advance.
Tim

I thing you haven't another way to read directory structure in unix(or nt doesn't matter) and get list of files except :

using ORACLE EXTERNAL PROCEDURE

and write small c/c++ (may be Java) function, that create list of files and return it in PL/SQL block.

I got this from asktom.com and I have explained below how to use this daemon in your pl/sql to get the file names.

I have tried it and it works like a charm !!!

hope this helps
-----------------------------
This is a quick and dirty daemon -- written in csh (the cool shell)..

Here is a PL/SQL subroutine you can install in your schema:

create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/

Here is a C-Shell script you can run in the background (use this shell script
make sure it is named host.csh)

-------------------- bof ----------------------------
#!/bin/csh -f

sqlplus user/password <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh

set serveroutput on

declare
status number;
command varchar2(255);
begin
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
else
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );
end if;
end;
/
spool off
"EOF"

chmod +x tmp.csh
exec tmp.csh
----------------------- EOF ---------------------------------

SQL> exec host( 'ls -l' );
SQL> exec host( 'echo Hello World' );


Your can then do some thing like ...
Begin
host('ls > file_list');
read file names from file_list using UTL_FILE
...
...
end ;

Thanks for your help.

Hi,
I tried the above..it doesnt seem to work for me:(
Hes how i went about!!
1.Created the procedure
2.created the script and started it in the background.and it created this tmp.csh file (an empty one)
3.executed the procedure
It just said procedure successfully completed...and the other window closed in which the background process was going on...and the tmp.csh was still empty
Can u please tell me as to where would i have gone wrong.
thank you

Did you give the correct username/password for sqlplus in host.csh. This will be the username under which you compiled the procedure.
Make sure the background process is always runing by
typing the unix command ps -ef | grep unix_user.

This is what you should be able to see if everything works fine.
sqlplus > exec host( 'ls -l' );

The output of ls -l will apprear in the inix window.

Good luck !!

Hi
Thanx for the reply...
The background process is running continously
and when ina diff window i do
sql>exec host('ls');
it just says ..successfully xecutes..and shows no o/p int the wondow where i can c the backgorund process going on..
Thank you