Thursday, May 29, 2008

FND_GLOBAL affected by New Global Performance Changes

After applying ATG Rollup 5 patch (and above) we discovered an issue with some of our custom developments.
For some processes we got the following errors:

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250

and this:

ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been
detected in
fnd_global.set_nls.set_paramenters('NLS_LANGUAGE','AMERICAN').

After some debug work we found that this issue happens when executing FND_GLOBAL.apps_initialize more than once within a trigger/via a db link in the same transaction.

According to Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" this issue cause by a new global performance changes.

Oracle Development said: "Very sorry if the new global performance changes have exposed you to this error, but there is no way we can back out these changes. They are not only complex and wide spread but required to maintain functional performance levels. Using fnd_global to change user/resp context from a trigger is not only not supported it is ill advised."

OK, So we had to find a workaround to this issues and we found two...

I'll start with a sample of the new behavior of fnd_global to demonstrate the issue and the solutions/workarounds will come right after.

SQL> create table test1 (a number, b number);
Table created

SQL> insert into test1 (a) values (1001);
1 row inserted

SQL> insert into test1 (a) values (1002);
1 row inserted

SQL> commit;
Commit complete

SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       fnd_global.APPS_INITIALIZE(:new.a,1,1);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- ....
  9  end;
10  /
Trigger created

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
        -1

SQL> update test1 set b=1101 where a=1001;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1001

SQL> update test1 set b=1102 where a=1002;

update test1 set b=1102 where a=1002

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250
ORA-06512: at "APPS.TEST1_TRG_BI", line 2
ORA-04088: error during execution of trigger 'APPS.TEST1_TRG_BI'

As you can see, the second update failed because apps_initialize was executed for the second time in the same transaction.

Now I'll show two ways to workaround this issue:

1) As suggested in Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" a wrapper Concurrent Request which contain a call to the context set (apps_initialize) and afterwards submits the original request, is one possible solution.

instead:

create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     fnd_global.APPS_INITIALIZE(:new.a,1,1);
     ret_code := fnd_request.submit_request ('OWNER', 'ORIGINAL_CONC', . . .);
     . . .
     . . .
end;

create the following trigger:

create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     ret_code := fnd_request.submit_request ('OWNER', 'WRAPPER_CONC', . . . , :new.a, . . . );
     . . .
     . . .
end;

additionally - create a new plsql concurrent (WRAPPER_CONC) that contains the fnd_global.apps_initialize and submits the ORIGINAL_CONC concurrent request.

This way, the apps_initialize statement executed in a separate transaction with no error.

This is the preferred and recommended solution by Oracle.

2) The second solution is easier to implement, works fine but according to Note: 556391.1 is not supported since it contains calls to fnd_global within a database trigger.

Anyway...

The idea is to call the apps_initialize in an Autonomous Transaction procedure.

Follow this sample:

SQL> create or replace procedure test1_apps_init (p_user_id number) is
  2  pragma autonomous_transaction;
  3  begin
  4       fnd_global.APPS_INITIALIZE(p_user_id,1,1);
  5       commit;
  6  end;
  7  /

Procedure created

SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       test1_apps_init (:new.a);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- .....
  9  end;
10  /

Trigger created

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
        -1

SQL> update test1 set b=1101 where a=1001;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1001

SQL> update test1 set b=1102 where a=1002;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1002

As you can see, the update statements were executed successfully this time and the session was updated with the appropriate user context in each update statement.

Those two solutions are working fine, but keep in mind that the second is not supported.

You are welcome to leave a comment.

Aviad

8 comments:

Ivar Løvik said...

Great examples for demonstration the issues

Got this problem when using ADODB with the Oracle OLEDB Provider. The app called a stored proc with params (the apps_initialize was in this proc), even when terminating the instansiated object (ADODB.Command), this problem didn't go away !!!, guess there was some sort of provider connection cache or something, the connection string was the exact same for each run and they where within a loop, the command object got terminated each time, in the Oracle DB only a single connection was present, so I guess that this was seen as the same "session" from a oracle point of view (possible provider connection caching). Everything went ok on the first pass (not initialized), but failed on subsequent ones (already initialized)...

Seems like I was in for a long night, programming and testing...

But then.......

The problem is that you can't do more than one logical fnd_global.apps_initialize within a current session, right?, seems to me as we should'n have to either. If your code (from a .Net app, etc) actually does init several times in a single session, rewrite it to do so only once, if that's not an option, just check if the FND environment is already initalized before fnd_global.apps_initialize, do so by checking fnd_global.session_id <> 0 and perhaps fnd_global.user_id <> 0 and fnd_global.resp_id <> 0

works for me....

Aviad said...

Thanks for sharing this info', Ivar.

Aviad

Anonymous said...

Thanks a lot for the valuable solutions. It is really helpful and a quick work around.

Anonymous said...

EXCELLENT EXCELLENT EXCELENT!!! Thank you so much for this blog. It really jelped and saved big time hassles.

Thanks Again and keep up the good work

Anonymous said...

Connections from a Pool is another issue. It kinda defeats pooling altogether especially with custom Oracle ERP apps.

Anonymous said...

Thanks a lot for valuable information that you have shared. you could not think how this saved me.

Thanks a lot and keep it up.

Anonymous said...

Thank you so much!!

You have solved our problem that's pending on Oracle Support for 6 months.

Larry Ellison should really make you the head of Oracle Support division!

Anonymous said...

Ivar Løvik :
Thank you so much for the work-around!! This really helped