sql - ORACLe PROCEDURE - AUTHID 仅在模式级别允许

Question

在尝试通过过程创建表时，我遇到了类似

的错误

Error(73,9): PLS-00157: AUTHID only allowed on schema-level programs

PROCEDURE BCKUP 
AUTHID CURRENT_USER AS
statusmsg VARCHAR2(400);

BEGIN
    --Backup records
    EXECUTE IMMEDIATE 'CREATE TABLE schemaname.tabname AS SELECT pgm.* FROM XYZ pgm, IJK prf WHERE prf.col1=pgm.col1 AND prf.ID IN(SELECT ID FROM TAB2)';
    COMMIT;

EXCEPTION WHEN OTHERS THEN
    statusmsg :='SQL ERRROR CODE ' || SQLCODE|| ' -ERROR- ' ||SQLERRM;
    dbms_output.put_line('ERROR : ' || statusmsg);
END BCKUP;

Answer 1

您的程序似乎是一个包的一部分。在包中，您只能在包级别设置调用者权限 (AUTHID CURRENT_USER)。您不能为每个单独的程序设置它。

要么将调用者权限移动到包级别:

CREATE OR REPLACE PACKAGE pkg
AUTHID CURRENT_USER
AS

    PROCEDURE BCKUP;

    -- more types, procedures and functions

END pkg;


CREATE OR REPLACE PACKAGE BODY pkg
AS

    PROCEDURE BCKUP 

    statusmsg VARCHAR2(400);

    BEGIN
        --Backup records
        EXECUTE IMMEDIATE 'CREATE TABLE schemaname.tabname AS SELECT pgm.* FROM XYZ pgm, IJK prf WHERE prf.col1=pgm.col1 AND prf.ID IN(SELECT ID FROM TAB2)';
        COMMIT;

    EXCEPTION WHEN OTHERS THEN
        statusmsg :='SQL ERRROR CODE ' || SQLCODE|| ' -ERROR- ' ||SQLERRM;
        dbms_output.put_line('ERROR : ' || statusmsg);
    END BCKUP;

END pkg;

或者创建一个全局过程(即在模式级别而不是包级别):

CREATE OR REPLACE PROCEDURE BCKUP 
AUTHID CURRENT_USER AS
statusmsg VARCHAR2(400);

BEGIN
    --Backup records
    EXECUTE IMMEDIATE 'CREATE TABLE schemaname.tabname AS SELECT pgm.* FROM XYZ pgm, IJK prf WHERE prf.col1=pgm.col1 AND prf.ID IN(SELECT ID FROM TAB2)';
    COMMIT;

EXCEPTION WHEN OTHERS THEN
    statusmsg :='SQL ERRROR CODE ' || SQLCODE|| ' -ERROR- ' ||SQLERRM;
    dbms_output.put_line('ERROR : ' || statusmsg);
END BCKUP;