Error:app-fnd-01926:the Custom Event when-logon-changed raised unhandled EXCEPTION:ORA-06502:PL

In this Document

Symptoms

Cause

Solution

Applies To:Oracle Application Object library-version 11.5.9 to 12.1.3 [Release 11.5 to 12.1]
Oracle Applications Technology stack-version 11.5.9 to 12.1.3 [Release 11.5 to 12.1]
information in this document applies to any platform.
Symptoms

Problem Description:
-----------------------
When attempting to launch forms the following error occurs.

App-fnd-01926:the Custom Event when-logon-changed raised unhandled exception:ora-06502:pl/sql:numeric or value ErrorCA Use

Site level profile option value is Option:FND:Debug Log level is incorrect. It is showing as ' unexpected ' in database where as it should show the numeric value 6 instead of ' unexpected '. Once We enable level, trace for database, cause of error can be determined. Profile option value can be checked using below SQL.

Sql> SELECT SUBSTR (e.profile_option_name,1,25) profile,decode (a.level_id,10001, ' Site ', 10002, ' application ', 10003, ' Resp ', 10004, ' User ') l,decode (a.level_id,10001, ' Site ', 10002,c.application_short_name,10003, B.responsibility_name,10004,d.user_name) LVALUE,NVL (A.profile_option_value, ' is Null ') value,substr (a.last_update_ date,1,25) updated_date
2 from Fnd_profile_option_values A, fnd_responsibility_tl B, fnd_application c,fnd_user D, fnd_profile_options E
3 WHERE e.profile_option_name = ' Aflog_level '
4 and e.profile_option_id = a.profile_option_id and A.level_value = b.responsibility_id (+)
5 and A.level_value = c.application_id (+) and A.level_value = d.user_id (+)
6 ORDER by Profile_option_name;

Profile L LVALUE VALUE updated_date
------------------------- -----------     -------------        ------------------                 ------------------------ ---------
Aflog_level site site Unexpected Mon DD YYYY HH:MM:SS

Profile Option:FND:Debug Log level should is set correctly. It should not null or should is showing varchar value in database. It should show the numeric value and this is the reason of error "Ora-06502:pl/sql:numeric or value error". Default value for this profile option is ' unexpected ' if it saved in database then it'll be save in numeric value which corresponds to 6.

The main cause of the reported error is corrupt or invalid CUSTOM.PLL. First we should be making sure that CUSTOM.PLL used are able to generate CUSTOM.PLX successfully.

For 11i use the below command to generate the CUSTOM.PLX from CUSTOM.PLL:

F60gen \
module= $AU _TOP/RESOURCE/CUSTOM.PLL \
USERID=APPS/XXX \
output_file= $AU _TOP/RESOURCE/CUSTOM.PLX \
Module_type=library Batch=yes compile_all=special

And for r12.0.x and r12.1.x use the below command:

Frmcmp_batch \
module= $AU _TOP/RESOURCE/CUSTOM.PLL \
USERID=APPS/XXX \
output_file= $AU _TOP/RESOURCE/CUSTOM.PLX \
Module_type=library Batch=yes compile_all=special

If there is any error during generation of CUSTOM.PLX then it should being rectified first before checking the profile option Value explained above. CUSTOM.PLL should be correct one and should able to generate CUSTOM.PLX as it is being read during launching of forms. If There'll be is any uncompiled packages written inside CUSTOM.PLL then it'll not read correctly during forms launch and would end in error.

Solution

1. Update the profile option value, Option:FND:Debug Log level from unexpected to 6 using below SQL commands.

Connect to SQLPLUS as APPS User:

Sql> CREATE TABLE Fnd_profile_option_values_bkup as SELECT * from Fnd_profile_option_values;

Sql> Select profile_option_id from fnd_profile_options where profile_option_name= ' aflog_level ';

Take the profile Option ID.

Sql> SELECT * from Fnd_profile_option_values where profile_option_id=3099;

sql> Update fnd_profile_option_values set profile_option_value= ' 6 ' where profile_option_id=3099 and level_id=10001;

Sql> commit;

Sql> SELECT SUBSTR (e.profile_option_name,1,25) profile,decode (a.level_id,10001, ' Site ', 10002, ' application ', 10003, ' Resp ', 10004, ' User ') l,decode (a.level_id,10001, ' Site ', 10002,c.application_short_name,10003, B.responsibility_name,10004,d.user_name) LVALUE,NVL (A.profile_option_value, ' is Null ') value,substr (a.last_update_ date,1,25) updated_date
From Fnd_profile_option_values A, fnd_responsibility_tl B, fnd_application c,fnd_user D, fnd_profile_options E
WHERE e.profile_option_name = ' Aflog_level '
and e.profile_option_id = a.profile_option_id and A.level_value = b.responsibility_id (+)
and A.level_value = c.application_id (+) and A.level_value = d.user_id (+)
ORDER by Profile_option_name;

It should show now the correct profile option value as 6.

2. Stop all Appstier services.

3. ' Regenerate Form Files ' using Adadmin utility. It should be successful.

4. Start all Appstier services.

5. Retest the issue.

Error:app-fnd-01926:the Custom Event when-logon-changed raised unhandled EXCEPTION:ORA-06502:PL