系统下的过程无效

Question

我在系统架构下有一个成功编译的过程。

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;

[.. 它是在系统模式下创建的，因为我不允许在 X$FRBMSFT 上向用户"edi"授予选择]。

我授予用户"edi"执行此过程的权限。[.. 连接为系统，角色 SYSDBA，我执行grant execute on system.get_file_list to EDI; ]

当我尝试使用用户"edi"执行过程（execute system.get_file_list;）时，它返回错误

PLS-00905：对象系统。GET_FILE_LIST无效

有人可以给我一个提示，告诉我我做错了什么吗？

谢谢

Answer 1

最后，我设法创建了该过程，并得到了@APC提供的链接的一些帮助。

。作为系统连接

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;

。作为 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;

区别在于它们被称为使用用户 SYSTEM 创建的对象的方式。他们被召唤 SYS.xxx 而不是 SYSTEM.xxx