Reading all the filenames in a directory

[TimGoff]

Hello,

Does anyone know how I could use PL/SQL to find all the file names that are in a specific folder?

Thanks,
Tim

 

[jimirvine]

Try this:

create or replace
procedure get_dir_list
    (p_directory varchar2)
    is
        l_null      varchar2(100);
        l_directory varchar2(100);
    BEGIN
         l_directory := p_directory;
         sys.dbms_backup_restore.searchfiles(l_directory, l_null);
    FOR x IN (select fname_krbmsft fname from x$krbmsft) LOOP
        dbms_output.put_line(x.fname);
   END LOOP;
 
   END;

 

[TimGoff]

jimirvine,
Thanks for your quick response.
I'm running 9i unfortunately at the moment - I believe I read dbms_backup_restore.searchfiles is only available in 10g?
Thanks,
Tim

 

[jimirvine]

oops. Sorry about that.

Here is one nicked from AskTom, it uses java tho, so it might not be suitable for your request.:

--Create a temporary table to store the directory onbjects
 create global temporary table DIR_LIST
    ( filename varchar2(255) )
    on commit delete rows;
 
--create the java proc
 create or replace
       and compile java source named "DirList"
    as
    import java.io.*;
    import java.sql.*;
   
    public class DirList
    {
    public static void getList(String directory)
                      throws SQLException
   {
       File path = new File( directory );
       String[] list = path.list();
       String element;
   
       for(int i = 0; i < list.length; i++)
       {
           element = list[i];
           #sql { INSERT INTO DIR_LIST (FILENAME)
                  VALUES (:element) };
       }
   }
   
   }
   */

--create the stored proc
 create or replace
    procedure get_dir_list( p_directory in varchar2 )
    as language java
    name 'DirList.getList( java.lang.String )';
    /
 
 --populate the temp table   
 exec get_dir_list( '/documentation' );

--view the results
 select * from dir_list ;

PS the last one was not my code, but cadged from eBrian over at orafaq

 

[TimGoff]

Thanks, never tried it before but will give it a go...

 

[TimGoff]

Hi
I tried running it and receive the following error. Would you have any idea what the problem is?

SQL> exec get_dir_list( '/MRU_MUSIC_FILE' );

begin get_dir_list( '/MRU_MUSIC_FILE' ); end;

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "UKTV_OWN.GET_DIR_LIST", line 0
ORA-06512: at line 1

 

[SantaMufasa]

Tim,

So that we can offer alternatives for your need, is your usage context such that you are running from a SQL*Plus environment, or must the directory-reading code be embedded inside (and run from) a stored procedure?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TimGoff]

Dave
I would like to run the routine from inside a PL/SQL stored procedure (the files are passed to another routine, and the data imported into the db.)
Tim

 

[SantaMufasa]

Thanks, Tim.

To be more precise in my question, is there any point (prior to each invocation of the PL/SQL stored procedure), that you (can) use SQL*Plus (for example, to begin the execution of the PL/SQL stored procedure)?

If so, then there is a simple method to create a listing of any directory's contents (on the machine on which the client connection resides), which your PL/SQL stored procedure can then access as a table.

Let us know if this scenario fits your situation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[TimGoff]

Dave,
I'm only calling the procedure from SQL*Plus as a test. Normally it will be called from within another stored procedure. I'm looking to get the directory contents of a folder which sits on the Oracle server rather though - don't know if that makes a difference?
Thanks for your continued help, I appreciate it.
Tim