java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

We got this error in Apache jserv.log file:

[Oct 31, 2007 10:19:12 AMEDT]: 1193840352057:Thread[Thread-1045,10,main]:-1:-1:ebiz11i.justanexample.com:192.168.2.32:8168:16680:UNEXPECTED:[fnd.framework.OAExcept
ion]:Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java
.sql.SQLException: ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with session parameters

has been detected in fnd_global.put(PERMISSION_CODE,FND_PERMIT_0001).

ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 428
ORA-06512: at "APPS.FND_GLOBAL", line 447
ORA-06512: at "APPS.FND_GLOBAL", line 2376
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 821
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1127
ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421

ORA-06512: at line 1

We applied DB patch 5890966.

Oracle advised us to follow the steps given in unpublished metalink note 388315.1


Symptoms

When user tries to log in to Applications this does not succeed, but raises the error

You have encountered an unexpected Exception. Please contact system administrator for assistance.

The stack trace reported when selecting Click here for exception details (*) and log entries in the jserv.log report the following error:


java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1800
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 302
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 455
ORA-06512: at line 1

(*) This link is only shown when FND: Diagnostics profile is set to Yes for this user.

Cause
This error is raised when the profile ICX_SESSION_TIMEOUT [ICX:Session Timeout] has a decimal value (e.g. 30.33333333) instead of an integer value (e.g. 30). When an user has languages setting which use ',' instead of '.' as decimal separator (like FR, NL, DE) the STRING to NUMBER conversion for this profile option value fails with the ORA-6502 PL/SQL: numeric or value error: character to number conversion error seen here.

The value for ICX_SESSION_TIMEOUT profile is set when running Autoconfig. In the script afwebprf.sql the following calculation is done to obtain the value.

(%s_sesstimeout%)/(60*1000)

This is to make the ICX_SESSION_TIMEOUT equal to jserv session timeout.

The default value for %s_sesstimeout% is 1800000 which gives a result of '30' which is fine. However when a different value like 2000000 is used here this calculation will result in '33.33333333' which is not correct. The value should be truncated so an integer value is saved here. This behavior has been reported in bug 5478368.

Solution
The error can be resolved by making sure that profile ICX_SESSION_TIMEOUT [ICX:Session Timeout] has an integer value. When it's not possible to run Autoconfig the following work-around can be implemented.

Open the maintenance form for the profile options and retrieve the ICX:Session Timeout profile

Remove the decimal part from the value, like '33.33333' becomes '33'

Save the changes

Bounce Apache server

However since the profile option is calculated during an Autoconfig run the same error will reappear when Autoconfig is run.

The permanent solution is the following

Open the context editor to maintain the .xml file storing the values used by Autoconfig

Change the value for %s_sesstimeout% to the default 1800000 or a value for which the calculation also returns an integer value (like 1860000 = 31, 1920000 = 32, etc)

Run Autoconfig to have this change implemented

Bounce Apache server