Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, March 13, 2009

afdbprf.sh fails with ORA-12899: value too large for column

Vickie faced this error while running adconfig on a newly upgraded 10.2.0.4 home:

$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/03122349/adconfig.log

In the autoconfig log $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/03122349/adconfig.log shows:

afdbprf.sh started at Thu Mar 12 23:49:43 EDT 2009
Executable : $ORACLE_HOME/bin/sqlplus


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 23:49:44 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : FND_DB_WALLET_DIR
Level Id : 10001
New Value : $ORACLE_HOME/appsutil/wallet
Old Value : $9.2.0_ORACLE_HOME/appsutil/wallet
Updated profile option value - 1 row(s) updated
Application Id : 174
Profile Name : ECX_UTL_XSLT_DIR
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
Updated profile option value - 1 row(s) updated
Application Id : 174
Profile Name : ECX_UTL_LOG_DIR
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
Updated profile option value - 1 row(s) updated
Application Id : 0
Profile Name : BIS_DEBUG_LOG_DIRECTORY
Level Id : 10001
New Value : /usr/tmp
Old Value : /usr/tmp
declare
*
ERROR at line 1:
ORA-12899: value too large for column
"APPLSYS"."FND_PROFILE_OPTION_VALUES"."PROFILE_OPTION_VALUE" (actual: 480,
maximum: 240)
ORA-06512: at line 44
ORA-06512: at line 139


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ERRORCODE = 1 ERRORCODE_END
.end std out.

.end err out.
****************************************************
Hope this helps!



Metalink Note 458511.1 gives a solution:

Cause

Script afdbprf.sql called by afdbprf.sh is having the following included which caused the issue:

-- Set up UTL_FILE_LOG profile option
--
set_profile(1, 'UTL_FILE_LOG',
10001, 0,
'%s_db_util_filedir%',
NULL);

As we see in the above code the UTL_FILE_LOG profile option is filled with the value of the s_db_util_filedir

The value of utl_file_dir is over 240 characters , FND_PROFILE_OPTION_VALUES:PROFILE_OPTION_VALUE varchar2(240) is limited to 240 characters.

According to Bug 6404909 the FND_PROFILE_OPTION_VALUES:PROFILE_OPTION_VALUE varchar2(240) can not be (easily) changed because there are many, many locations within C-code user exits and other places in which a hardcoded value of 240 for this column exist.
Solution

To implement the solution, please execute the following steps:

1 - Change the value dbutilfiledir in the database context file ($ORACLE_HOME/appsutil/$CONTEXT_NAME.xml) to a value less the 240 characters

2 - run autoconfig again.

This solved the problem.