Hello,

I am using dbms_backup_restore.searchfiles & X$KRBMSFT to get the list of the files from a certain path but it returns me also some other files which were opened and closed with an editor in the past.

More exactly, I have 2 files in the path (1.txt and 2.txt). I am opening 1.txt with GEDIT and close it.

Then, I execute my PLSQL script which uses the 2 objects mentioned above.

When I am selecting from X$KRBMSFT, it returns me also the 1,txt~ in the list, but as far as I know, this is used a file which is opened.

The strange thing is that even if 1.txt was closed in the GEDIT, it is still returned in the list.

Can you tell me how to make it do not appear in X$KRBMSFT table, please?

Here is my code:

conected as system, I create the following objects:

create a new user, EDI.

CREATE USER EDI IDENTIFIED BY 02021979 DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

GRANT CONNECT, RESOURCE, CREATE ANY directory, CREATE ANY PROCEDURE TO EDI;

create or replace view file_list as select fname_krbmsft from X$KRBMSFT readonly;

create or replace procedure searchfiles (pattern in out nocopy varchar2, ns in out nocopy varchar2) authid definer as
begin
    dbms_backup_restore.searchfiles(pattern, ns);
end searchfiles;

GRANT SELECT ON FILE_LIST TO EDI;
GRANT EXECUTE ON SEARCHFILES TO EDI;

connect as EDI.

create table temp_EDI_file_list
    ( dir varchar2(50)
    , file_name  varchar2(200)
    );

create or replace package pkg_main as

    PROCEDURE GET_FILE_LIST;
       
end pkg_main;

create or replace package body pkg_main as

    procedure get_file_list as
    
        ns              varchar2(1024);
        directory_path like varchar2(1024) := '/home/oracle/EDI';
    begin
    
        -- before generating the file list, the temporary table is deleted
        delete from edi.temp_EDI_file_list;
        
        -- it reads the contents of my_directory into a table called X$FRBMSFT
        sys.SEARCHFILES (directory_path, ns);
            
        for each_file in (select regexp_substr(fname_krbmsft, '[^\/]+$') as name from sys.file_list) loop
            insert into temp_edi_file_list
            values (each_directory.directory_name, each_file.name);
        end loop;
        
        commit;
        
        exception
            when others then 
                raise_application_error (-20001,sqlcode || ' ' || sqlerrm);
        
    END GET_FILE_LIST;
    
end pkg_main;

Execute the get_file_list procedure:

execute pkg_main.get_file_list;

Check the list of the files:

select * from edi.temp_edi_file_list;

... as result, it is returned 1.txt, 2.txt and, also, 1.txt~

Thank you,
Ionut Preda.