CODEWITHSUNDEEP
oracle-databaseplsql
mikcutu
mikcutu

Reputation: 1092

procedure under SYSTEM is invalid

I have a successfully compiled procedure under SYSTEM schema.

create or replace procedure get_file_list as

    ns      varchar2(1024);
    cursor c_my_directory is
        select directory_name, directory_path from all_directories where directory_path like '/home/oracle/EDI%';
begin

    -- before generating the file list, the temporary table is deleted
    delete from edi.temp_EDI_file_list;

    for each_directory in c_my_directory loop
        -- it reads the contents of my_directory into a table called X$FRBMSFT
        sys.dbms_backup_restore.searchfiles (each_directory.directory_path, ns);

        for each_file in (select fname_krbmsft as name from X$KRBMSFT) loop
            insert into edi.temp_edi_file_list
            values (each_directory.directory_name, each_file.name);
        end loop;
    end loop;

    commit;

    exception
        when others then 
            raise_application_error (-20001,sqlcode || ' ' || sqlerrm);

end get_file_list;

[.. it was created under SYSTEM schema because I am not allowed to grant select on X$FRBMSFT to user "edi"].

I granted execute privilegies to user "edi" on this procedure. [.. connected as SYSTEM, role SYSDBA, I executed grant execute on system.get_file_list to EDI;]

When I am trying to execute the procedure (execute system.get_file_list;) with user "edi" it return the error

PLS-00905: object SYSTEM.GET_FILE_LIST is invalid

Can someone, please, give me a hint about what am I doing wrong?

Thank you,

Upvotes: 1

Views: 350

Answers (1)

mikcutu
mikcutu

Reputation: 1092

In the end I managed to create the procedure, with some help from the link provided by @APC.

... conected as SYSTEM

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;

... conected as EDI

create or replace procedure get_file_list as

    ns      varchar2(1024);
    cursor c_my_directory is
        select directory_name, directory_path from all_directories where directory_path like '/home/oracle/EDI%';
begin

    -- before generating the file list, the temporary table is deleted
    delete from edi.temp_EDI_file_list;

    for each_directory in c_my_directory loop
        -- it reads the contents of all directories into a table called X$FRBMSFT via procedure SEARCHFILES
        sys.SEARCHFILES (each_directory.directory_path, ns);

        -- it interogate the X$FRBMSFT via file_list view
        for each_file in (select 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;
    end loop;

    commit;

    exception
        when others then 
            raise_application_error (-20001,sqlcode || ' ' || sqlerrm);

end get_file_list;

The difference was made by the way they were called the objects created with user SYSTEM. They were called with SYS.xxx instead of SYSTEM.xxx

Upvotes: 1

Related Questions