现在的位置: 首页 > 综合 > 正文

(2007 6月4日)oracle:create context引发ORA-01031,ORA-06512错误

2013年09月06日 ⁄ 综合 ⁄ 共 4121字 ⁄ 字号 评论关闭
    开发的同事, 提交一个 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.

抱歉!评论已关闭.