EBS R12 , 12.2 -- MULTI_ORG, MO_GLOBAL.SET_ORG_ACCESS ORA-01031 insufficient privileges, APP-PO-14142, MO_GLOBAL, DBMS_SESSION Line 101

Recently encountered an error in EBS R12 where a Multiple Organization setup was done.
The Standard forms screens was encountering APP-PO-14142 errors.

The error message was saying that there was an ORA-01031 produced in the database level. This error is actually the insufficient privilege error and it is normally produced when there is lack of privileges detected in the database level. So, the apps session must have trying to use a database object without having the necessary grants.

After some investigation , and putting some debug logs into the standard code, we have seen the everyting was in place. The schema that was used by the session was APPS and all the grants of the APPS schema was proper  ( still, just in case, we have recreated the grants and synonyms , as well as executed adgrants.sql)

We have taken an FND debug and analyzed the trace file , using the following well known method;

select max(log_sequence) from fnd_log_messages
eg : log_sequence = 71666646
2) Go to the System Profile options and set the following parameters:
FND: Debug Log Enabled = YES
FND: Debug Log Filename = NULL
FND: Debug Log Level STATEMENT (most detailed log)
FND: Debug Log Module %
3) Re-login to the application and reproduce the bug.
4) select message_text from fnd_log_messages where log_sequence > XXX – value from step 1

During the analysis , we have also seen that the org_id , security group id, application id, responsibility id etc. were all proper.

Using oradebug and/or alter system set events ‘1031 trace name errorstack level 3; , we have also gathered the call trace  and it was like the following; didnt helped much as the names of the function were interrnal and they were not documented or known

Calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36 call kgdsdst() 000000000 ? 000000000 ?
7FFF2A394E08 ? 000000001 ?
000000001 ? 000000002 ?
ksedst1()+98 call skdstdst() 000000000 ? 000000000 ?
7FFF2A394E08 ? 000000001 ?
000000000 ? 000000002 ?
ksedst()+34 call ksedst1() 000000000 ? 000000001 ?
7FFF2A394E08 ? 000000001 ?
000000000 ? 000000002 ?
dbkedDefDump()+2741 call ksedst() 000000000 ? 000000001 ?
7FFF2A394E08 ? 000000001 ?
000000000 ? 000000002 ?
ksedmp()+36 call dbkedDefDump() 000000003 ? 000000000 ?
7FFF2A394E08 ? 000000001 ?
000000000 ? 000000002 ?
dbkdaKsdActDriver() call ksedmp() 000000003 ? 000000000 ?
+1960 7FFF2A394E08 ? 000000001 ?
000000000 ? 000000002 ?
dbgdaExecuteAction( call dbkdaKsdActDriver() 7F01CAED9710 ? 7FFF2A39BA90 ?
)+1065 7FFF2A394E08 ? 000000001 ?
000000000 ? 000000002 ?
dbgdaRunAction()+81 call dbgdaExecuteAction( 7F01CAED9710 ? 00A184340 ?
0 ) 0020C0003 ? 7FFF2A39BA90 ?
000000001 ? 000000002 ?
dbgdRunActions()+59 call dbgdaRunAction() 7F01CAED9710 ? 000000005 ?
0020C0003 ? 7FFF2A39BA90 ?
000000001 ? 000000002 ?
dbgdProcessEventAct call dbgdRunActions() 7F01CAED9710 ? 000000005 ?
ions()+651 0020C0003 ? 7FFF2A39BA90 ?
000000001 ? 000000002 ?
dbgdChkEventKgErr() call dbgdProcessEventAct 7F01CAED9710 ? 00BBC01C0 ?
+1653 ions() 7F01CA9BF698 ? 7FFF2A39BA90 ?
000000001 ? 000000002 ?
dbkdChkEventRdbmsEr call dbgdChkEventKgErr() 7F01CAED9710 ? 00BBC01C0 ?
r()+56 7FFF2A391640 ? 7FFF2A39BA90 ?
000000001 ? 000000002 ?
ksfpec()+61 call dbkdChkEventRdbmsEr 7FFF2A391640 ? 00BBC01C0 ?
r() 7FFF2A391640 ? 7FFF2A39BA90 ?
000000001 ? 000000002 ?
dbgePostErrorKGE()+ call ksfpec() 7FFF2A391640 ? 7FFF2A391640 ?
1129 7FFF2A391640 ? 7FFF2A39BA90 ?
000000001 ? 000000002 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 00BBC01C0 ? 7F01CA990040 ?
66 000000407 ? 000000000 ?
100000000 ? 000000002 ?
kgeade()+351 call dbkePostKGE_kgsf() 00BBC01C0 ? 7F01CA990040 ?
000000407 ? 000000000 ?
100000000 ? 000000002 ?
kgerelv()+135 call kgeade() 00BBC01C0 ? 00BBC0370 ?
7F01CA990040 ? 000000407 ?
100000000 ? 000000002 ?
kserecl0()+157 call kgerelv() 00BBC01C0 ? 7F01CA990040 ?
000000407 ? 009D95D14 ?
7FFF2A39C900 ? 000000000 ?
kzctxChkTyp()+402 call kserecl0() 00BBC01C0 ? 7F01CA990040 ?
000000407 ? 000A39118 ?
074F6DB00 ? 074F6DB38 ?
kzctxesc()+341 call kzctxChkTyp() 7F01CA9DE998 ? 000000000 ?
000000009 ? 000A39118 ?
074F6DB00 ? 074F6DB38 ?
pevm_icd_call_commo call kzctxesc() 7F01CA9DE998 ? 000000005 ?
n()+867 7F01CA9A3DE0 ? 000A39118 ?
000000000 ? 074F6DB38 ?
pfrinstr_ICAL()+163 call pevm_icd_call_commo 00A5C6D80 ? 000000000 ?
n() 000000001 ? 000000007 ?
000000005 ? 7FFF00000000 ?
pfrrun_no_tool()+63 call pfrinstr_ICAL() 7F01CA9DE998 ? 24856BD10 ?
7F01CA9DEA08 ? 000000007 ?
000000005 ? 7FFF00000000 ?
pfrrun()+622 call pfrrun_no_tool() 7F01CA9DE998 ? 24856BD10 ?
7F01CA9DEA08 ? 000000007 ?
000000005 ? 7FFF00000000 ?
plsql_run()+644 call pfrrun() 7F01CA9DE998 ? 000000000 ?
7F01CA9DEA08 ? 7FFF2A39DD80 ?
000000005 ? 069D984CC ?

We have also reverse engineered the code (MO_GLOBAL) and seen that it was failing at DBMS_SESSION Line 101.

Line 101 of DBMS_SESSION was something like the following;

KZCTXESC(attribute1,attribute2.....);

the KZCTXESC was an internal C function  and Oracle was using it through a gateway interface probably declared with the following PRAGMA definition in the DBMS_SESSION;

PROCEDURE SOMEFUCTION(attribute1,attribute2.....);
PRAGMA INTERFACE (C, SOMEFUNCTION);

So , this was an Internal mechanism and it was closed to us , thus the issue can not be investigated more after this level.

Also look : undocumented pragmas http://www.petefinnigan.com/weblog/archives/00000835.htm

Also, the problem in the DBMS_SESSION.. It just could not be tested directly and it was misleading to test the dbms_session by using a direct plsql approach, as to set the attribute of a context it is necessary to use the package/procedure declared when the context was created.

That is ;

begin
dbms_session.set_context('multi_org', 'access_mode', 'S');
end;
--> Can not be done, it will produce ORA-01031 in any cases, as MO_GLOBAL should be used to set this context..
->
select type,schema, package from dba_context where namespace = 'MULTI_ORG';
ACCESSED LOCALLY APPS MO_GLOBAL

Anyways, altough the investigations and finding the solution took 4 hours of mine ; I ll keep it short :)

Then, we have took a Forms Trace with binds and analyzed the trace file.

In that trace file, we saw a very interesting thing.

There was a very interesting line, as the standard context name was there , I mean multi_org, but it was written as MULTİ_ORG.. An uppercase "İ" was there, so the database session must have been using the word MULTİ_ORG for using the multi_org context and failing because the MULTİ_ORG with an uppercase İ was not equal with multi_org actually.

By keeping it in mind, checked the recently modified profile option values in the system and saw ICX_NLS_SORT, ICX: NLS Sort was changed to XTURKISH , which was actually the extended Turkish.

Then we connected to the database with sqlplus and set my session's nls_sort to XTURKISH, and used the following query to check the situation;

select nls_upper(multi_org) from dual;

The output of the query was MULTİ_ORG as I was expected.

So at that point we concluded that there must be some uppercase function used in the EBS code and that was changing the name of the context that was trying to be set by the database session from multi_org to MULTİ_ORG , which was actually a different word than multi_org.

We also concluded that, DBMS_SESSION Line 101 could have getting ORA-01031 insufficient privileges because it could not find the context that the session was trying set , as there was actually  no context named MULTİ_ORG in the database.

As for the solution;

We changed the the ICX NLS SORT profile from XTURKISH to BINARY and solved the problem ..

It was a very interesting story , that's why I shared this with you.

It means, chaging a EBS profile, actually changes the behave and output the code and I didnt like it at all. It seem like a bug actually, as an internal APPS context should not be dependent on this kind of profile change..  Anayways, we know the solution for that now :)

The system that we have encountered this problem was the latest Version, 12.2.4.

So, in any case, I ll report that to Oracle, as well.