CREATE OR REPLACE FUNCTION LIST_FILES (lp_string IN VARCHAR2 default null, path$ VARCHAR2)
RETURN file_array pipelined AS
 
lv_pattern VARCHAR2(1024);
lv_ns VARCHAR2(1024);
 
BEGIN
SELECT directory_path
    INTO lv_pattern
    FROM dba_directories
   WHERE directory_name = '/srv/media';

  lv_pattern := lv_pattern || '/' || path$;
 
SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(path$, lv_ns);
 
FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
FROM X$KRBMSFT
WHERE FNAME_KRBMSFT LIKE '%'|| NVL(lp_string, FNAME_KRBMSFT)||'%' ) LOOP
PIPE ROW(file_list.file_name);
END LOOP;
 
END;
/

select * from table(list_files(NULL, '47A_Repository\17_22-10-2012\47_26\ФГУП Ростехинвентаризация\41_466_002_000000420_0100_00000_АФОНИЧКИН'))

SQL> 
SQL> create type file_array as table of varchar2(100)
  2  /
Type created

SQL> 
SQL> CREATE OR REPLACE FUNCTION LIST_FILES (lp_string IN VARCHAR2 default null)
  2  RETURN file_array pipelined AS
  3  
  4  lv_pattern VARCHAR2(1024);
  5  lv_ns VARCHAR2(1024);
  6  
  7  BEGIN
  8  
  9  SELECT directory_path
 10  INTO lv_pattern
 11  FROM dba_directories
 12  WHERE directory_name = 'NFS_DIR';
 13  
 14  SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns);
 15  
 16  FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
 17  FROM X$KRBMSFT
 18  WHERE FNAME_KRBMSFT LIKE '%'|| NVL(lp_string, FNAME_KRBMSFT)||'%' ) LOOP
 19  PIPE ROW(file_list.file_name);
 20  END LOOP;
 21  
 22  END;
 23  /
Function created

SQL> grant execute on LIST_FILES to rti;
Grant succeeded

SQL> create public synonym list_files for sys.LIST_FILES;
Synonym created

SQL> select * from table(list_files);
COLUMN_VALUE
--------------------------------------------------------------------------------

SQL> 
SQL> CREATE OR REPLACE FUNCTION LIST_FILES (lp_string IN VARCHAR2 default null)
  2  RETURN file_array pipelined AS
  3  
  4  lv_pattern VARCHAR2(1024);
  5  lv_ns VARCHAR2(1024);
  6  
  7  BEGIN
  8  
  9  SELECT directory_path
 10  INTO lv_pattern
 11  FROM dba_directories
 12  WHERE directory_name = 'RTI_MEDIA';
 13  
 14  SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns);
 15  
 16  FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
 17  FROM X$KRBMSFT
 18  WHERE FNAME_KRBMSFT LIKE '%'|| NVL(lp_string, FNAME_KRBMSFT)||'%' ) LOOP
 19  PIPE ROW(file_list.file_name);
 20  END LOOP;
 21  
 22  END;
 23  /
Function created

SQL> select * from table(list_files);
select * from table(list_files)
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 5940
ORA-06512: at "SYS.LIST_FILES", line 14
ORA-06512: at line 1

SQL> 
SQL> CREATE OR REPLACE FUNCTION LIST_FILES (lp_string IN VARCHAR2 default null, path$ VARCHAR2)
  2  RETURN file_array pipelined AS
  3  
  4  lv_pattern VARCHAR2(1024);
  5  lv_ns VARCHAR2(1024);
  6  
  7  BEGIN
  8  
  9  SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(path$, lv_ns);
 10  
 11  FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
 12  FROM X$KRBMSFT
 13  WHERE FNAME_KRBMSFT LIKE '%'|| NVL(lp_string, FNAME_KRBMSFT)||'%' ) LOOP
 14  PIPE ROW(file_list.file_name);
 15  END LOOP;
 16  
 17  END;
 18  /
Warning: Function created with compilation errors

SQL> select * from table(list_files(NULL, '47A_Repository\17_22-10-2012\47_26\ФГУП Ростехинвентаризация\41_466_002_000000420_0100_00000_АФОНИЧКИН'))
  2  ;
COLUMN_VALUE
--------------------------------------------------------------------------------

SQL>  var pt varchar2(128);
SQL>  var ns varchar2(10)
SQL> ;
SQL> exec :pt := '/srv/media/DIMA'; :ns := null;
PL/SQL procedure successfully completed
pt
---------
/srv/media/DIMA
ns
---------

SQL> exec dbms_backup_restore.searchFiles(:pt, :ns);
begin dbms_backup_restore.searchFiles(:pt, :ns); end;
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 5940
ORA-06512: at line 1
pt
---------
/srv/media/DIMA
ns
---------

SQL> 
SQL> [18:40:17] Александер Торопченов (Караби): alter system set db_recovery_file_dest_size=100M scope=memory;
Warning: connection was lost and re-established
[18:40:17] Александер Торопченов (Караби): alter system set db_recovery_file_dest_size=100M scope=memory
ORA-00900: invalid SQL statement
SQL> alter system set db_recovery_file_dest = '/srv/oradata' scope=memory;
System altered
SQL> -- set serveroutput on
SQL> DECLARE
  2  pattern VARCHAR2(1024) := '/srv/oracle';
  3  ns VARCHAR2(1024);
  4  BEGIN
  5  SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
  6  FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
  7  DBMS_OUTPUT.PUT_LINE(each_file.name);
  8  END LOOP;
  9  END;
 10  /
PL/SQL procedure successfully completed

SQL> create type file_array as table of varchar2(1000);
  2  /
create type file_array as table of varchar2(1000);
ORA-00955: name is already used by an existing object

SQL> create type file_array force as table of varchar2(100);
  2  /
create type file_array force as table of varchar2(100);
ORA-00955: name is already used by an existing object

SQL> drop type file_array;
Type dropped

SQL> create type file_array as table of varchar2(1000);
  2  /
Type created

SQL>