开发的同事, 提交一个 create context 的语句, 在应用调用一直出ORA-01031、ORA-06512错, 对方强调说测试分配了DBA role就可解决, 但实际上, 生产环境,不能给一个业务的用户分配DBA role, 另外,测试中,就算分配DBA给该oracle用户,还是同样的错, 最后,在asktom找到解决方法:
An elegant solution but I'm receiving an error whenever I access DBMS_SESSION despite running as a user with DBA privilege (8i v8.1.7) variable c REFCURSOR; exec MSCR_PAYABLEEMPTIES( :c, 'O'); ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 58 ORA-06512: at "MIS.MSCR_PAYABLEEMPTIES", line 16 ORA-06512: at line 1 Attached is a subset of the procedure that triggers the error: CREATE OR REPLACE PACKAGE "MIS"."P_REF_CURSOR" is type ret_ref_cursor is ref cursor; end p_ref_cursor; CREATE OR REPLACE CONTEXT MSCR_CTX_PAYABLEEMPTIES USING MSCR_PAYABLEEMPTIES; CREATE OR REPLACE PROCEDURE "MIS"."MSCR_PAYABLEEMPTIES" ( Result_Cursor IN OUT p_ref_cursor.ret_ref_cursor, Direction IN VARCHAR2 DEFAULT NULL, EndDate IN VARCHAR2 DEFAULT NULL ) AS l_query varchar2(9999); BEGIN l_query := 'SELECT DIRECTION,TRADE,VESSELCODE,VOYAGENUMBER,LOADPORTCODE,' || 'DISCHPORTCODE,EQUIPMENTTYPE,NOOFCONTAINERS,TEUS,CONTRTEUS ' || 'FROM MISFLATMVOYAGE WHERE COMMODITYCODE = ''RE00'''; -- Check whether a direction parameter has been passed -- N.B. If passed from the web reports module it will contain '' instead of NULL to -- indicate a blank parameter IF ( Direction IS NOT NULL ) AND ( Direction <> '''' ) THEN dbms_session.set_context('MSCR_CTX_PAYABLEEMPTIES', 'DIRECTION', Upper(Direction)); l_query := l_query || ' AND DIRECTION = sys_context(''MSCR_CTX_PAYABLEEMPTIES'', ''DIRECTION'' )'; END IF; open Result_Cursor FOR l_query; END MSCR_PAYABLEEMPTIES;
Followup September 4, 2002 - 8am US/Eastern:
Well, it doesn't have anything to do with DBA/not DBA. You can execute dbms_session -- the 1031 is coming from the fact that "MIS"."MSCR_PAYABLEEMPTIES" is not able to set the context MSCR_CTX_PAYABLEEMPTIES. I notice your procedure and package have "MIS". on them. But the context does not. If you ran this script as a user OTHER THEN "MIS", then the context can only be set by that SCHEMA.MSCR_PAYABLEEMPTIES -- not by MIS.MSCR_PAYABLEEMPTIES; Perhaps that is it. Do a select * from dba_context and make sure the SCHEMA is in fact MIS. Here is a small test case showing the "issue" ops$tkyte@ORA817DEV.US.ORACLE.COM> drop context MSCR_CTX_PAYABLEEMPTIES; Context dropped. ops$tkyte@ORA817DEV.US.ORACLE.COM> drop user a cascade; User dropped. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> create user a identified by a; User created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> grant create session to a 2 / Grant succeeded. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE CONTEXT MSCR_CTX_PAYABLEEMPTIES USING MSCR_PAYABLEEMPTIES; Context created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE PROCEDURE "A"."MSCR_PAYABLEEMPTIES" 2 AS 3 BEGIN 4 dbms_session.set_context('MSCR_CTX_PAYABLEEMPTIES', 'DIRECTION', 'x' ); 5 END MSCR_PAYABLEEMPTIES; 6 / Procedure created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect a/a ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off a@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST a@ORA817DEV.US.ORACLE.COM> set termout on a@ORA817DEV.US.ORACLE.COM> a@ORA817DEV.US.ORACLE.COM> a@ORA817DEV.US.ORACLE.COM> exec MSCR_PAYABLEEMPTIES BEGIN MSCR_PAYABLEEMPTIES; END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 62 ORA-06512: at "A.MSCR_PAYABLEEMPTIES", line 4 ORA-06512: at line 1 a@ORA817DEV.US.ORACLE.COM> a@ORA817DEV.US.ORACLE.COM> @connect / a@ORA817DEV.US.ORACLE.COM> set termout off ops$tkyte@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout on ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE CONTEXT MSCR_CTX_PAYABLEEMPTIES USING A.MSCR_PAYABLEEMPTIES; Context created. ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> @connect a/a ops$tkyte@ORA817DEV.US.ORACLE.COM> set termout off a@ORA817DEV.US.ORACLE.COM> REM GET afiedt.buf NOLIST a@ORA817DEV.US.ORACLE.COM> set termout on a@ORA817DEV.US.ORACLE.COM> a@ORA817DEV.US.ORACLE.COM> a@ORA817DEV.US.ORACLE.COM> exec MSCR_PAYABLEEMPTIES PL/SQL procedure successfully completed.