1 2 Previous Next 18 Replies Latest reply on Jun 15, 2015 11:51 AM by Loris Medici .AKTEK.

    Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error

    Loris Medici .AKTEK.

      Hi all,


      I'm an Oracle EBS (R11 and R12) developer and I use Oracle SQL Developer 4.1. To browse data in R12 installations I have to use MO_GLOBAL.SET_POLICY_CONTEXT API but I can't manage doing that when working with SQL Developer. (Same code works fine with SQL Plus or TOAD...)


      See below:


      EXEC MO_GLOBAL.SET_POLICY_CONTEXT('S', 101)

      Error report -

      ORA-01031: insufficient privileges

      ORA-06512: at "SYS.DBMS_SESSION", line 101

      ORA-06512: at "APPS.MO_GLOBAL", line 1167

      ORA-06512: at line 1

      01031. 00000 -  "insufficient privileges"

      *Cause:    An attempt was made to perform a database operation without

                 the necessary privileges.

      *Action:   Ask your database administrator or designated security

                 administrator to grant you the necessary privileges


      Note: I'm connected with APPS user which has the ALTER SESSION privilege.


      Because of this problem I can't use SQL Developer while developing on R12 systems, have to use TOAD instead. (Which I absolutely dislike... I'm not interested in any tool other than SQL Developer.)


      Any suggestions?


      Regards & thanks in advance,

      Loris.


        • 1. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
          thatJeffSmith-Oracle

          What's your connect string in SQL*Plus vs SQL Developer?

           

          My only guess is you're not connected the way you THINK you are, as we're running the code exactly as SQL*Plus or any other 3rd party tool would be.

          • 2. Re: Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
            Loris Medici .AKTEK.

            Thanks for the reply Jeff.

             

            My connection type is TNS (not Basic), therefore I use exactly the same TNSNAMES.ORA/SQLNET.ORA files/settings as SQL Plus and TOAD.

             

            Nevertheless, the connection string shown in the Connections Dialog is below:


            (DESCRIPTION=

              (ADDRESS= (PROTOCOL=TCP) (HOST=xxx.xxx.xxx.xxx) (PORT=xxxx))

              (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=xxxxxxx))

            )

             

            Regards,

            Loris.

            • 3. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
              thatJeffSmith-Oracle

              you're not using a proxy user, or there's not some trigger that fires if you log in using sqlplus vs sqldev?

               

              you're getting that ORA-01031 from the database, not from us

              • 4. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                Mkirtley-Oracle

                Hi Loris,

                    To try and diagnose why you get the ORA-01031 you could try setting the event to trap the errors.

                I don't know if it'll work from SQL*Developer but you could try -

                 

                alter session set events '1031 trace name errorstack level 3';

                 

                then run the procedure.  This should create a trace file on the RDBMS.

                If it doesn't work through SQL*Developer then on the RDBMS you could try setting it at the system level -

                 

                alter system set events '1031 trace name errorstack level 3';

                 

                The trace should show the SQL causing the error.

                If you set it at the system level then turn it off once you have the trace file -

                 

                alter system set events '1031 trace name context off';

                 

                Regards,

                Mike

                1 person found this helpful
                • 5. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                  PaoloM

                  Hi Loris,

                  I put the following code in a worksheet and executed it with F5 while connected as the APPS user to our DEV eBS 12.1.3 instance and I am not able to replicate your error:

                   

                  EXEC MO_GLOBAL.SET_POLICY_CONTEXT('S', 101);
                  
                  

                   

                  As a result I only get:

                   

                  anonymous block completed
                  
                  

                   

                  and no errors.

                   

                  Hope it helps,

                  Paolo

                  1 person found this helpful
                  • 6. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                    Loris Medici .AKTEK.

                    Thanks for the test Paolo, I get this error with the APPS user.

                     

                    I guess I will have to resort to Mike's suggestion to see what happens on the database side. I have this on our all 6 R12 installations, but the all systems are set up from the same template. (Different patch levels though...)

                     

                    Will return with further information later.

                     

                    Regards,

                    Loris.

                    • 7. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                      Loris Medici .AKTEK.

                      Hi Mike,

                       

                      I have the trace file on hands, pasting relevant parts below: (Anyone understand this?)

                       

                      Trace file /orahome/oracle/SAFGYOT/11.2.0/admin/SAFGYOT_safgyotestdb/diag/rdbms/safgyot/SAFGYOT/trace/SAFGYOT_ora_12297.trc
                      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                      With the Partitioning, OLAP, Data Mining and Real Application Testing options
                      ORACLE_HOME = /orahome/oracle/SAFGYOT/11.2.0
                      System name:      Linux
                      Node name:  safgyotestdb
                      Release:    2.6.32-358.el6.x86_64
                      Version:    #1 SMP Tue Jan 29 11:47:41 EST 2013
                      Machine:    x86_64
                      VM name:    VMWare Version: 6
                      Instance name: SAFGYOT
                      Redo thread mounted by this instance: 1
                      Oracle process number: 53
                      Unix process pid: 12297, image: oracle@safgyotestdb


                      *** 2015-05-21 12:27:30.928
                      *** SESSION ID:(241.9473) 2015-05-21 12:27:30.928
                      *** CLIENT ID:() 2015-05-21 12:27:30.928
                      *** SERVICE NAME:(SAFGYOT) 2015-05-21 12:27:30.928
                      *** MODULE NAME:(SQL Developer) 2015-05-21 12:27:30.928
                      *** ACTION NAME:() 2015-05-21 12:27:30.928

                      dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
                      ----- Error Stack Dump -----
                      ORA-01031: insufficient privileges
                      ----- Current SQL Statement for this session (sql_id=5vnj088319f72) -----
                      BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S', 101); END;

                      *** 2015-05-21 12:27:31.698
                      ----- PL/SQL Stack -----
                      ----- PL/SQL Call Stack -----
                      object      line object
                      handle    number name
                      0xde2b91a0       101  package body SYS.DBMS_SESSION
                      0xccadaf20      1167  package body APPS.MO_GLOBAL
                      0x77757ef0         1  anonymous block

                      ----- Call Stack Trace -----
                      calling              call     entry                argument values in hex     
                      location             type     point                (? means dubious value)    
                      -------------------- -------- -------------------- ----------------------------
                      skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                        7FFF5C4B7268 ? 000000001 ?
                        000000001 ? 000000002 ?
                      ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                      7FFF5C4B7268 ? 000000001 ?
                        000000000 ? 000000002 ?
                      ksedst()+34          call     ksedst1()            000000000 ? 000000001 ?
                        7FFF5C4B7268 ? 000000001 ?
                        000000000 ? 000000002 ?
                      dbkedDefDump()+2741  call     ksedst()             000000000 ? 000000001 ?
                        7FFF5C4B7268 ? 000000001 ?
                        000000000 ? 000000002 ?
                      ksedmp()+36          call     dbkedDefDump()       000000003 ? 000000000 ?
                        7FFF5C4B7268 ? 000000001 ?
                      000000000 ? 000000002 ?
                      dbkdaKsdActDriver()  call     ksedmp()             000000003 ? 000000000 ?
                      +1960 7FFF5C4B7268 ? 000000001 ?
                        000000000 ? 000000002 ?
                      dbgdaExecuteAction(  call     dbkdaKsdActDriver()  7F8AD4F50710 ? 7FFF5C4BDEF0 ?
                      )+1065 7FFF5C4B7268 ? 000000001 ?
                        000000000 ? 000000002 ?
                      dbgdaRunAction()+81  call     dbgdaExecuteAction(  7F8AD4F50710 ? 00A163C00 ?
                      0                             )                    0020C0003 ? 7FFF5C4BDEF0 ?
                        000000001 ? 000000002 ?
                      dbgdRunActions()+59  call     dbgdaRunAction()     7F8AD4F50710 ? 000000005 ?
                        0020C0003 ? 7FFF5C4BDEF0 ?
                        000000001 ? 000000002 ?
                      dbgdProcessEventAct  call     dbgdRunActions()     7F8AD4F50710 ? 000000005 ?
                      ions()+651 0020C0003 ? 7FFF5C4BDEF0 ?
                        000000001 ? 000000002 ?
                      dbgdChkEventKgErr()  call     dbgdProcessEventAct  7F8AD4F50710 ? 00BBA9380 ?
                      +1653                         ions()               7F8AD4B8F698 ? 7FFF5C4BDEF0 ?
                        000000001 ? 000000002 ?
                      dbkdChkEventRdbmsEr  call     dbgdChkEventKgErr()  7F8AD4F50710 ? 00BBA9380 ?
                      r()+56                                             7FFF5C4B3AA0 ? 7FFF5C4BDEF0 ?
                        000000001 ? 000000002 ?
                      ksfpec()+61          call     dbkdChkEventRdbmsEr  7FFF5C4B3AA0 ? 00BBA9380 ?
                      r()                  7FFF5C4B3AA0 ? 7FFF5C4BDEF0 ?
                        000000001 ? 000000002 ?
                      dbgePostErrorKGE()+  call     ksfpec()             7FFF5C4B3AA0 ? 7FFF5C4B3AA0 ?
                      1129 7FFF5C4B3AA0 ? 7FFF5C4BDEF0 ?
                        000000001 ? 000000002 ?
                      dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   00BBA9380 ? 7F8AD4B63FA8 ?
                      66 000000407 ? 000000000 ?
                      100000000 ? 000000002 ?
                      kgeade()+351         call     dbkePostKGE_kgsf()   00BBA9380 ? 7F8AD4B63FA8 ?
                        000000407 ? 000000000 ?
                      100000000 ? 000000002 ?
                      kgerelv()+135        call     kgeade()             00BBA9380 ? 00BBA9530 ?
                        7F8AD4B63FA8 ? 000000407 ?
                        100000000 ? 000000002 ?
                      kserecl0()+157       call     kgerelv()            00BBA9380 ? 7F8AD4B63FA8 ?
                        000000407 ? 009D77974 ?
                        7FFF5C4BED60 ? 000000000 ?
                      kzctxChkTyp()+1119   call     kserecl0()           00BBA9380 ? 7F8AD4B63FA8 ?
                        000000407 ? 7FFF5C4BEDD8 ?
                        7FFF5C4BEAA0 ? 7FFF5C4BED80 ?
                      kzctxesc()+341       call     kzctxChkTyp()        7F8AD4BAEB60 ? 0A0F29140 ?
                        000000009 ? 7FFF5C4BEDD8 ?
                        7FFF5C4BEAA0 ? 7FFF5C4BED80 ?
                      pevm_icd_call_commo  call     kzctxesc()           7F8AD4BAEB60 ? 000000005 ?
                      n()+867 7F8AD4B73030 ? 7FFF5C4BEDD8 ?
                        000000000 ? 7FFF5C4BED80 ?
                      pfrinstr_ICAL()+163  call     pevm_icd_call_commo  00A5A4240 ? 000000000 ?
                      n()                  000000001 ? 000000007 ?
                        010000005 ? 7FFF00000000 ?
                      pfrrun_no_tool()+63  call     pfrinstr_ICAL()      7F8AD4BAEB60 ? 0D43B9570 ?
                      7F8AD4BAEBD0 ? 000000007 ?
                        010000005 ? 7FFF00000000 ?
                      pfrrun()+622         call     pfrrun_no_tool()     7F8AD4BAEB60 ? 0D43B9570 ?
                        7F8AD4BAEBD0 ? 000000007 ?
                        010000005 ? 7FFF00000000 ?
                      plsql_run()+644      call     pfrrun()             7F8AD4BAEB60 ? 000000000 ?
                        7F8AD4BAEBD0 ? 7FFF5C4C01E0 ?
                        010000005 ? 08ED41018 ?

                      *** 2015-05-21 12:27:32.902
                      peicnt()+296         call     plsql_run()          7F8AD4BAEB60 ? 000000001 ?
                        000000000 ? 7FFF5C4C01E0 ?
                        010000005 ? 000000000 ?
                      kkxexe()+520         call     peicnt()             7FFF5C4C01E0 ? 7F8AD4BAEB60 ?
                        7F8AD4B72BF0 ? 7FFF5C4C01E0 ?
                      7F8AD4B702F0 ? 000000000 ?
                      opiexe()+17780       call     kkxexe()             7F8AD49C8058 ? 7F8AD4BAEB60 ?
                        000000000 ? 7FFF5C4C01E0 ?
                      7F8AD4B702F0 ? 000000000 ?
                      kpoal8()+2219        call     opiexe()             000000049 ? 000000003 ?
                        7FFF5C4C1850 ? 7FFF5C4C01E0 ?
                        7F8AD4B702F0 ? 000000000 ?
                      opiodr()+916         call     kpoal8()             00000005E ? 00000001C ?
                        7FFF5C4C4980 ? 7FFF5C4C01E0 ?
                        7F8AD4B702F0 ? 100000001 ?
                      ttcpip()+2755        call     opiodr()             00000005E ? 00000001C ?
                        7FFF5C4C4980 ? 000000000 ?
                        009C4A8F0 ? 100000001 ?
                      opitsk()+1712        call ttcpip()             00BBBF010 ? 00958A170 ?
                        7FFF5C4C4980 ? 000000000 ?
                        7FFF5C4C43D8 ? 7FFF5C4C4B74 ?
                      opiino()+961         call     opitsk()             00BBBF010 ? 000000001 ?
                        7FFF5C4C4980 ? 000000000 ?
                        7FFF5C4C43D8 ? 7FFF5C4C4B74 ?
                      opiodr()+916         call     opiino()             00000003C ? 000000004 ?
                        7FFF5C4C60F8 ? 000000000 ?
                        7FFF5C4C43D8 ? 7FFF5C4C4B74 ?
                      opidrv()+565         call     opiodr()             00000003C ? 000000004 ?
                      7FFF5C4C60F8 ? 000000000 ?
                        009C4A3A0 ? 7FFF5C4C4B74 ?
                      sou2o()+98           call     opidrv()             00000003C ? 000000004 ?
                      7FFF5C4C60F8 ? 000000000 ?
                        009C4A3A0 ? 7FFF5C4C4B74 ?
                      opimai_real()+128    call     sou2o()              7FFF5C4C60D0 ? 00000003C ?
                        000000004 ? 7FFF5C4C60F8 ?
                        009C4A3A0 ? 7FFF5C4C4B74 ?
                      ssthrdmain()+252     call     opimai_real()        000000002 ? 7FFF5C4C62C0 ?
                        000000004 ? 7FFF5C4C60F8 ?
                        009C4A3A0 ? 7FFF5C4C4B74 ?
                      main()+196           call     ssthrdmain()         000000002 ? 7FFF5C4C62C0 ?
                        000000001 ? 000000000 ?
                      009C4A3A0 ? 7FFF5C4C4B74 ?
                      __libc_start_main()  call     main()               000000002 ? 7FFF5C4C6468 ?
                      +253 000000001 ? 000000000 ?
                        009C4A3A0 ? 7FFF5C4C4B74 ?
                      _start()+36          call     __libc_start_main()  000A0BD94 ? 000000002 ?
                        7FFF5C4C6458 ? 000000000 ?
                        009C4A3A0 ? 7FFF5C4C4B74 ?

                      <snip snip snip snip --------------------------------------------------------------------- snip snip snip snip>

                       

                      ========== FRAME [19] (kzctxChkTyp()+1119 -> kserecl0()) ==========
                      defined by frame pointers 0x7fff5c4bee40 and 0x7fff5c4bed80
                      CALL TYPE: call   ERROR SIGNALED: yes   COMPONENT: (null)
                      RDI 000000000BBA9380 RSI 00007F8AD4B63FA8 RDX 0000000000000407
                      RCX 00007FFF5C4BEDD8 R8 00007FFF5C4BEAA0 R9 00007FFF5C4BED80
                      RAX 0000000000000000 RBX 00000000A0F29140 RBP 00007FFF5C4BEE40
                      R10 00007FFF5C4BED60 R11 0000000000000000 R12 0000000000000009
                      R13 0000000000000000 R14 00007F8AD4BAEB60 R15 0000000000000009
                      RSP 00007FFF5C4BED90 RIP 0000000001F5168F
                      Dump of memory from 0x7fff5c4bed80 to 0x7fff5c4bee40
                      7FFF5C4BED80 5C4BEE40 00007FFF 01F51694 00000000  [@.K\............]
                      7FFF5C4BED90 5C4BEE30 00007FFF 00000000 00000000  [0.K\............]
                      7FFF5C4BEDA0 5C4BEE38 00007FFF 00000000 00000000  [8.K\............]
                      7FFF5C4BEDB0 544C554D 524F5F49 D4B88A47 00007F8A  [MULTI_ORG.......]
                      7FFF5C4BEDC0 5C4BF290 00007FFF 0315CE7E 00000000  [..K\....~.......]
                      7FFF5C4BEDD0 00000009 00000001 00000000 00000000  [................]
                      7FFF5C4BEDE0 746C756D 726F5F69 00000067 00000000  [multi_org.......]
                      7FFF5C4BEDF0 00000000 00000000 00000000 00000000  [................]
                      7FFF5C4BEE00 00000005 00000000 D4BAEBD0 00007F8A  [................]
                      7FFF5C4BEE10 0BBA9530 00000000 00000000 00000000  [0...............]
                      7FFF5C4BEE20 00000001 00000000 00000000 00000000  [................]
                      7FFF5C4BEE30 00000000 FFFFFFFF 00000000 00000000  [................]

                      <snip snip snip snip --------------------------------------------------------------------- snip snip snip snip>

                       

                      ========== FRAME [41] (_start()+36 -> __libc_start_main()) ==========
                      defined by frame pointers 0x0  and 0x7fff5c4c6430
                      CALL TYPE: call   ERROR SIGNALED: yes   COMPONENT: (null)
                      RDI 0000000000A0BD94 RSI 0000000000000002 RDX 00007FFF5C4C6458
                      RCX 0000000000000000 R8 0000000009C4A3A0 R9 00007FFF5C4C4B74
                      RAX 0000000000000000 RBX 0000000000000000 RBP 0000000000000000
                      R10 0000003C00000000 R11 0000000000000000 R12 0000000000A0BCB0
                      R13 00007FFF5C4C6450 R14 0000000000000000 R15 0000000000000000
                      RSP 00007FFF5C4C6440 RIP 0000000000A0BCD4

                      • 8. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                        Loris Medici .AKTEK.

                        Thanks again Jeff,

                         

                        I understand perfectly that the error comes from the database but I get this only when I send the command from SQL Developer and no other Oracle or 3rd party tool, therefore I attribute it to SQL Developer.

                         

                        If I'm not mistaking, SQL Plus and TOAD connect to Oracle RDBMS using the local client binaries (OCI?) whereas SQL Developer connects natively through JDBC. This is the first time I encounter such a problem with SQL Developer. (I use it since version 2 somehing and like it alot.)

                         

                        Regards,

                        Loris.

                        • 9. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                          Mkirtley-Oracle

                          Hi Loris,

                              The trace shows the problem is with the actual procedure -

                           

                          ----- Current SQL Statement for this session (sql_id=5vnj088319f72) -----
                          BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S', 101); END;


                          and the stack has -


                          ----- PL/SQL Stack -----
                          ----- PL/SQL Call Stack -----
                          object      line object
                          handle    number name
                          0xde2b91a0       101  package body SYS.DBMS_SESSION
                          0xccadaf20      1167  package body APPS.MO_GLOBAL
                          0x77757ef0         1  anonymous block


                          Do you know if the APPS has any privileges granted to it through ROLES ?

                          Could you make sure it has been granted privileges on the procedure directly and could you also make sure it has the 'ALTER SESSION' privilege granted directly and not through a role ?


                          Regards,

                          Mike

                          • 10. Re: Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                            Loris Medici .AKTEK.

                            Hi again Mike,

                             

                            Yes the procedure belongs to the APPS schema and APPS user has ALTER SESSION privilege granted directly:

                             

                            SELECT *

                              FROM DBA_SYS_PRIVS

                              WHERE GRANTEE = 'APPS'

                                AND PRIVILEGE = 'ALTER SESSION' ;

                             

                            Returns;

                             

                            GRANTEE     PRIVILEGE         ADM

                            -------     ---------         ---

                            APPS        ALTER SESSION     NO

                             

                            • 11. Re: Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                              Loris Medici .AKTEK.

                              I would like to add that I can execute the procedure when connected with SQL Plus or TOAD, same user...

                              • 12. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                                thatJeffSmith-Oracle

                                Have you tried a direct connection? Don't use TNS.

                                 

                                I'm wondering if you're on the database you think you are - SQL Developer could be reading a different TNS entry than you expect.

                                • 13. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                                  Loris Medici .AKTEK.

                                  Dear Jeff,

                                   

                                  Yes I've tried that also - same thing happens. (My understanding of SQL Developer using TNS is just for getting connection parameters from TNSNAMES.ORA instead of having the user enter them, after that the actual connection is always made in the same way?)

                                   

                                  I'm on the correct database too. (I just need that built-in to select data from editioned EBS tables, custom and/or non-editioned tables show the correct data...)

                                   

                                  Jeff & Mike, have you noticed the errors registered in the trace file? The error is in the server side native call to a function named kserecl0() and happens only when I try to run the code in SQL Developer.

                                   

                                  Regards,

                                  Loris.

                                  • 14. Re: Oracle EBS R12 + SQL Developer 4.1 MO_GLOBAL.SET_POLICY_CONTEXT execution error
                                    thatJeffSmith-Oracle

                                    If you think the issue is JDBC related - and I suppose it could be - enable THICK connections in the Preferences > Advanced page.

                                     

                                    Then you'll be running queries similarly as they would be in SQL*Plus. If it works then, then we have some solid proof there's something amiss with the JDBC driver.

                                    1 person found this helpful
                                    1 2 Previous Next