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.
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