我要在存储过程中创建临时表,需要加权限,用Authid Current_User这个语句可以实现,如下
CREATE OR REPLACE PROCEDURE WH_FICO_COST12(STAT_DATE VARCHAR2) Authid Current_User IS
STR VARCHAR2(1000);
BEGIN
STR := ' CREATE GLOBAL TEMPORARY TABLE WH_COSP_TEMP
ON COMMIT DELETE ROWS
AS SELECT * FROM COSP ';
EXECUTE IMMEDIATE STR;
COMMIT; END; 但是如果在包中使用的话不知道怎么加这个权限,下面这个语句是有问题的CREATE OR REPLACE PACKAGE BODY WH_TEMP IS
PROCEDURE WH_FICO_COST(STAT_DATE VARCHAR2) Authid Current_User IS STR VARCHAR2(1000);
RESULT1 VARCHAR2(5000);
BEGIN
STR := ' CREATE GLOBAL TEMPORARY TABLE WH_TEMP
ON COMMIT DELETE ROWS
AS SELECT * FROM TEMP ';
ETL.EXECSQL(STR,RESULT1);
COMMIT;
END; END;
CREATE OR REPLACE PROCEDURE WH_FICO_COST12(STAT_DATE VARCHAR2) Authid Current_User IS
STR VARCHAR2(1000);
BEGIN
STR := ' CREATE GLOBAL TEMPORARY TABLE WH_COSP_TEMP
ON COMMIT DELETE ROWS
AS SELECT * FROM COSP ';
EXECUTE IMMEDIATE STR;
COMMIT; END; 但是如果在包中使用的话不知道怎么加这个权限,下面这个语句是有问题的CREATE OR REPLACE PACKAGE BODY WH_TEMP IS
PROCEDURE WH_FICO_COST(STAT_DATE VARCHAR2) Authid Current_User IS STR VARCHAR2(1000);
RESULT1 VARCHAR2(5000);
BEGIN
STR := ' CREATE GLOBAL TEMPORARY TABLE WH_TEMP
ON COMMIT DELETE ROWS
AS SELECT * FROM TEMP ';
ETL.EXECSQL(STR,RESULT1);
COMMIT;
END; END;
authid current_user把保内procedure后面加的都拿掉
CREATE OR REPLACE PACKAGE BODY WH_TEMP Authid Current_User IS
PROCEDURE WH_FICO_COST(STAT_DATE VARCHAR2) IS
STR VARCHAR2(1000);
RESULT1 VARCHAR2(5000);
BEGIN
STR := ' CREATE GLOBAL TEMPORARY TABLE WH_TEMP
ON COMMIT DELETE ROWS
AS SELECT * FROM TEMP ';
ETL.EXECSQL(STR,RESULT1);
COMMIT;
END;
END;
CREATE OR REPLACE PACKAGE WH_TEMP
Authid Current_User
IS
...
END WH_TEMP;
/CREATE OR REPLACE PACKAGE BODY WH_TEMP
IS
PROCEDURE WH_FICO_COST(STAT_DATE VARCHAR2)
IS
...END WH_TEMP;
/
出错:ORA-01031: insufficient privileges
CREATE OR REPLACE PACKAGE WH_TEMP Authid Current_User IS PROCEDURE WH_FICO_COST(STAT_DATE VARCHAR2);END WH_TEMP;CREATE OR REPLACE PACKAGE BODY WH_TEMP IS
PROCEDURE WH_FICO_COST(STAT_DATE VARCHAR2) IS
STR VARCHAR2(1000);
RESULT1 VARCHAR2(5000);
BEGIN
STR := ' CREATE GLOBAL TEMPORARY TABLE WH_COSP_TEMP
ON COMMIT DELETE ROWS
AS SELECT * FROM COSP ';
ETL.EXECSQL(STR,RESULT1);
COMMIT;
END; END;