Home » SQL & PL/SQL » SQL & PL/SQL » Run through files with wildcard..?
Run through files with wildcard..? [message #260932] Tue, 21 August 2007 05:14 Go to next message
SaraM
Messages: 9
Registered: March 2007
Junior Member
Hi,

my problem:

I have a directory(utl_file) and a filename with wildcard (test*.dat).

In the package utl_file I can not find a function to run through all files which match a wildcard.

Does anyone know how I can get a list of all matches files in plsql ?

Thank you very much!!

Susanne
Re: Run through files with wildcard..? [message #260933 is a reply to message #260932] Tue, 21 August 2007 05:17 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
"reading files in a directory -- how to get a list of available files."

MHE
Re: Run through files with wildcard..? [message #261096 is a reply to message #260932] Tue, 21 August 2007 16:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 10g, you can consider using the following procedure:
SQL> host dir e:\files
 Volume in drive E is Data
 Volume Serial Number is 287D-3237

 Directory of e:\files

03/05/2007  10:32 PM    <DIR>          .
03/05/2007  10:32 PM    <DIR>          ..
05/17/2006  06:36 PM         1,369,430 file1.pdf
12/11/2006  07:03 PM         1,812,413 file2.pdf
05/10/2006  05:48 PM         4,136,752 file3.txt
05/10/2006  05:48 PM         4,136,752 file4.pdf
12/16/2006  09:49 AM         1,732,255 file5.pdf
05/10/2006  05:48 PM         4,136,752 file7.txt
               6 File(s)     17,324,354 bytes
               2 Dir(s)  14,188,064,768 bytes free

SQL> create or replace procedure get_dir_list
  2  (p_directory varchar2)
  3  is
  4      l_null      varchar2(100);
  5      l_directory varchar2(100);
  6  BEGIN
  7       l_directory := p_directory;
  8       sys.dbms_backup_restore.searchfiles(l_directory, l_null);
  9  FOR x IN (select fname_krbmsft fname from x$krbmsft) LOOP
 10       dbms_output.put_line(x.fname);
 11  END LOOP;
 12
 13  END;
 14  /

Procedure created.

SQL> exec get_dir_list('e:\files\*.pdf');
E:\FILES\file1.pdf
E:\FILES\file2.pdf
E:\FILES\file4.pdf
E:\FILES\file5.pdf

PL/SQL procedure successfully completed.

SQL>

This is an undocumented procedure and therefore may not be guaranteed to be in future releases, so use it at your peril.
Re: Run through files with wildcard..? [message #261156 is a reply to message #261096] Wed, 22 August 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is an undocumented procedure and therefore may not be guaranteed to be in future releases

But a nice trick. Wink

Regards
Michel
Re: Run through files with wildcard..? [message #261384 is a reply to message #261156] Wed, 22 August 2007 07:57 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I agree. It can definitely come in handy.
Previous Topic: how to execute this procedure
Next Topic: spooling choice of columns as log file
Goto Forum:
  


Current Time: Fri Mar 29 00:35:47 CDT 2024