ORA-06502: PL/SQL: numeric or value error: character to number conversion error 11i forms.

Dear Folks,

We have come across this issue in one of our EBS client runs on 11i version and  when we tried to open any forms eventually failing with below error immediately.

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

So, we got to know the actual issue when we directly accessed the forms.

https://ebsprod.kalicut-sensors.com/dev60cgi/f60cgi

We logged into using SYSADMIN and selected one of the responsibilities and below pop-pop window appeared. .

 

Hence, we pull off the package "FND_TIMEZONES and looked for line 42 & 55 as shown in above screenshot and identified  it throws error at below code.

function get_client_timezone_code return varchar2 is

begin

  return get_code(fnd_profile.VALUE ('CLIENT_TIMEZONE_ID'));

 end;

=======

So, precisely, it was issue with the "CLIENT_TIMEZONE_ID".

Since, we aren't having forms at this time, We looked when this profile got changed

Some one who had happened to be use apps password got to change the profile option value from backend (DB).

Below query we shows us "The profiles has changed recently for SYSADMIN"

column profile format a40

column value format a20

column level format a15

column app format a20

column resp format a20

column user format a20

select substr(fpo.user_profile_option_name,1,60) profile,

substr(fpov.profile_option_value,1,75) value,

fpov.profile_option_id,

decode(fpov.level_id,10001,

'SITE',10002,

'APPLICATION',10003,

'RESPONSIBILITY',

10004,'USER',10005,'SERVER')"level",

fa.application_short_name app,

substr(fr.responsibility_name,1,20)"responsibility",

substr(fu.user_name,1,10)"user"

from fnd_profile_option_values fpov,

fnd_profile_options_vl fpo,

fnd_application fa,

fnd_responsibility_vl fr,

fnd_user fu,

fnd_logins fl

where fpo.profile_option_id=fpov.profile_option_id

and fa.application_id(+)=fpov.level_value

and fr.application_id(+)=fpov.level_value_application_id

and fr.responsibility_id(+)=fpov.level_value

and fu.user_id(+)=fpov.level_value

and fl.login_id(+) = fpov.LAST_UPDATE_LOGIN

and fpo.user_profile_option_name in('Client Timezone') and fu.USER_NAME='SYSADMIN';

So, some one wrongly updated the value of this profile for SYSADMIN user.

PROFILE                 VALUE        PROFILE_OPTION_ID level      APP

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

responsibility                        user

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

Client Timezone             '                 3773 USER      FND

                               SYSADMIN

It shows above value  -- Colon ('). It shouldn't be this value.

Hence, we compare this profile with other non-prod instances and it was showed EMTPY value.

As soon as we updated above profile value to correct value. We were able to open forms.

Command we used - It changes profile option value at user level.

++++++++++++++

declare

retval boolean;

cursor cur is select * from fnd_user where user_name= 'SYSADMIN';

begin

for rec in cur

loop

dbms_output.put_line ( rec.user_name );

retval:=fnd_profile.save('CLIENT_TIMEZONE_ID','','USER',rec.user_id);

end loop;

end;

/ 

Thanks.