--CREATE PROCEDURE sp_CT_TrackByCntr_LatestStatus
CREATE OR REPLACE PROCEDURE sp_CT_TrackByCntr_LatestStatus(CNTR_NUM1 IN VARCHAR2,CNTR_NUM2 IN VARCHAR2,
CNTR_NUM3 IN VARCHAR2,CNTR_NUM4 IN VARCHAR2,CNTR_NUM5 IN VARCHAR2,CNTR_NUM6 IN VARCHAR2,RET_OUT OUT SYS_REFCURSOR)
IS
V_SQL varchar2(3000);
BEGIN
V_SQL := 'CREATE GLOBAL TEMPORARY TABLE TEMP_CNTR(CNTR_NUM CHAR(10)) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE V_SQL;
OPEN RET_OUT FOR select * from user_info;
END;
/
--普通调用 SP
SET SERVEROUTPUT ON SIZE 10000;
DECLARE
CURSOR1 SYS_REFCURSOR;
V user_info%rowtype;
BEGIN
sp_CT_TrackByCntr_LatestStatus('A','A','A','A','A','A',CURSOR1);
loop
fetch CURSOR1 into V;
exit when CURSOR1%notfound;
dbms_output.put_line(V.USERID);
end loop;
close CURSOR1;
END;
/
调用SP的时候 EXECUTE IMMEDIATE V_SQL;这一句 为什么有问题?
ORA-01031: 权限不足
ORA-06512: 在 "EB_MAINT.SP_CT_TRACKBYCNTR_LATESTSTATUS", line 7
ORA-06512: 在 line 6我用户是有创建权限的
...
351 rows selected
该有的权限都有吧
CREATE GLOBAL TEMPORARY TABLE TEMP_CNTR(CNTR_NUM varchar2(100)) ON COMMIT PRESERVE ROWS;
declare
v_1 varchar2(100);
str varchar2(500);
begin
v_1:='测试人员';
str := 'INSERT INTO TEMP_CNTR (CNTR_NUM) VALUES (:1)';
EXECUTE IMMEDIATE str USING v_1;
commit;
end;我这样试了下是好的 为什么在存储过程里面就不行呢
在存储过程中通过角色授予的权限和直接授予的权限是有不同的
没有sys密码,用自己的赋了个权限,居然好了。grant create table to your_user_name;汗
Authid Current_User
IS
BEGIN
...
END原来这样就好