Accessing Unix Files in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Accessing Unix Files in PL/SQL

  1. #1
    Join Date
    Sep 2001
    Posts
    6
    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

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  3. #3
    Join Date
    May 2000
    Posts
    58
    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 ;


  4. #4
    Join Date
    Sep 2001
    Posts
    6
    Thanks for your help.

  5. #5
    Join Date
    Jun 2001
    Posts
    316
    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

  6. #6
    Join Date
    May 2000
    Posts
    58
    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 !!



  7. #7
    Join Date
    Jun 2001
    Posts
    316
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width