Read all files in a directory from a procedure.

For this aim you can use different ways.

The most used function UTL_FILE.FOPEN only accept a one file, it doesn´t accept * or a directory.

1 way:

Use DBMS_BACKUP_RESTORE. It,s from 10g version.

n VARCHAR2(1024);
SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(DIRECTORY_NAME, n);

 FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP 
          dbms_output.put_line(each_file.name); 
END LOOP; 

With this solution you can filter the result to get only a type of files based in a extension or based in name,etc.

SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT
WHERE FNAME_KRBMSFT LIKE '%.txt%'

Maybe you need grants

  • grant execute on sys.dbms_backup_restore to hr;

If you have problems to read X$KRBMSFT table you can create a view and give grant to see that view to your user

  • create view vw_x$krbmsft as select * from sys.X$KRBMSFT;
  • grant select on sys.vw_x$krbmsft to hr;

And then change the select

SELECT FNAME_KRBMSFT AS name FROM sys.vw_x$krbmsft

2 way:

A java procedure  https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584

 

 

Anuncios