ORA-06512: at "APPS.FND_CORE_LOG", line 318

Shoaib pinged me today. He was getting this error duing AutoConfig on DB node:

begin

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.FND_CORE_LOG", line 318
ORA-06512: at "APPS.FND_CORE_LOG", line 62
ORA-06512: at "APPS.FND_CORE_LOG", line 456
ORA-06512: at "APPS.FND_PROFILE", line 110
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 7

A search on My Oracle Support revealed R12: ORA-06502: PL/SQL: numeric or value error trying to update table fnd_profile_option_values [ID 1501822.1]


However the error described was similar, but not same:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "APPS.FND_PROFILE", line 731
ORA-06512: at "APPS.FND_PROFILE", line 963
ORA-06512: at "APPS.FND_PROFILE", line 3282
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 33

If you compare the two, you'll see that line numbers are different in the error we were facing.

So I decided to check the code:

select line,text
dba_source
where name='FND_CORE_LOG'
and line between 316 and 321

316

if UTL_DIR is null and P_DIRECTORY is null then
317
-- Then determine the utl_file_dir value.
318
select translate(ltrim(value),',',' ')
LINE
----------
TEXT
--------------------------------------------------------------------------------
319
into TEMP_DIR
320
from v$parameter
321
where lower(name) = 'utl_file_dir';

I checked for the length of the variable TEMP_DIR:

select line,text from dba_source where name='FND_CORE_LOG' and text like '%TEMP_DIR%'


SQL> /
LINE
----------
TEXT
--------------------------------------------------------------------------------
309
TEMP_DIR varchar2(512);

So TEMP_DIR is a 512 character variable. Then I took the length of the value which was being stuffed into it:

SQL> select length(translate(ltrim(value),',',' '))

2 from v$parameter
3 where lower(name) = 'utl_file_dir';

LENGTH(TRANSLATE(LTRIM(VALUE),',',''))
--------------------------------------
1069

It is clear now that if you try to stuff a 1069 character string into a 512 character variable, you'll get
ORA-06502: PL/SQL: numeric or value error: character string buffer too small


So I asked Shoaib to modify the utl_file_dir to keep it within the 512 characters limit, as we should not change Oracle code by modifying oracle provided seeded packages like APPS.FND_CORE_LOG.

Once Shoaib, reduced the no. of directories listed in utl_file_dir and the character count reduced to less than 512, autoconfig succeeded without errors.