Get the list of all the files by using DBMS_BACKUP_RESTORE

DECLARE
   pattern VARCHAR2(1024) := '/u01/oracle/admin/SID/udump';
   ns VARCHAR2(1024);
BEGIN
   SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
   FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT WHERE FNAME_KRBMSFT LIKE '%.trc') 
   LOOP
 DBMS_OUTPUT.PUT_LINE(each_file.name);
   END LOOP;
END;
/

Note: it only available in 10g
Reference: http://www.chrispoole.co.uk/tips/plsqltip2.htm

Get the list of files in SQL*Plus using java source

Before 10g we cann't get list of all the files directly in Oracle. Because Oracle doesn't know about the OS Directory structure. for that we have to use java or any other language. like


-- First we will create a list to keep all the file names.
create or replace type List as table of varchar2(255);
/


-- Create a java source to get all the files from given directory.
create or replace and compile java source named "FileList"
as
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class FileList
{
public static ARRAY getList(String directory) throws SQLException
{
File path = new File( directory );
Connection conn =
new OracleDriver().defaultConnection();
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor( "LIST", conn );
return new ARRAY( descriptor, conn, path.list() );
}
}
/

-- Function to use the java source & get the output.
CREATE OR REPLACE FUNCTION GETFILES_FUN( DIR_PATH_V VARCHAR2) return List
AS language java name 'FileList.getList(java.lang.String )
return oracle.sql.ARRAY.ARRAY';
/


-- Select to select all the files in directory.

select * from TABLE ( cast (GETFILES_FUN( 'c:\\' ) as List) );



Note: I have tried it in Windows only.