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.

java.lang.IllegalArgumentException: Illegal argument for colorScheme applet parameter

Today Jayabharath Velugoti pinged me, about forms not launching in an 11i instance. When I tried reproducing the issue, I go this in my laptop java console:

 Following Exception occured: java.lang.IllegalArgumentException: Illegal argument for colorScheme applet parameter

java.lang.IllegalArgumentException: Illegal argument for colorScheme applet parameter
at oracle.forms.engine.Main.initDesktop(Unknown Source)
at oracle.forms.engine.Main.start(Unknown Source)
at sun.plugin2.applet.Plugin2Manager$AppletExecutionRunnable.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)


A search on support.oracle.com revealed this article: Opening Any Form Applications Gives: Illegal Argument For Color Scheme Applet Parameter. [ID 858367.1]

However this article was relevant to R12. Anyhow, I executed the query given in the article:

col NAME for a25

col LEV for a4
col CONTEXT for a30
col VALUE for a12
select po.profile_option_name "NAME",
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
from FND_PROFILE_OPTIONS po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp
where po.profile_option_name like 'FND_COLOR_SCHEME'
and pov.application_id = po.application_id
and pov.profile_option_id = po.profile_option_id
and usr.user_id (+) = pov.level_value
and rsp.application_id (+) = pov.level_value_application_id
and rsp.responsibility_id (+) = pov.level_value
and app.application_id (+) = pov.level_value
order by "NAME", pov.level_id, "VALUE"

NAME LEV CONTEXT VALUE

------------------------- ---- ------------------------------ ------------
FND_COLOR_SCHEME SITE SWAN
FND_COLOR_SCHEME USER JOHNDOE SWAN
FND_COLOR_SCHEME USER JANEDOE SWAN

That didn't look right. This is an 11i instance. Swan interface is a new feature introduced in R12. So I asked Jayabharath about it and he said that this instance was an R12 instance and was recently cloned again as an 11i instance. That doesn't explain it as the source 11i instance didn't have SWAN. Possibly, someone did this by mistake. Anyways, I updated the values from SWAN to BLUE:   SQL> Declare
value Boolean;
Begin
value := fnd_profile.save('FND_COLOR_SCHEME','BLUE','SITE');
End;
/
2 3 4 5 6
PL/SQL procedure successfully completed.

SQL> UPDATE FND_PROFILE_OPTION_VALUES
2 SET PROFILE_OPTION_VALUE='BLUE'
3 WHERE PROFILE_OPTION_VALUE='SWAN';
2 rows updated.

SQL> COMMIT;

The query showed BLUE now:

NAME LEV CONTEXT VALUE

------------------------ ---- ------------------------------ ------------
FND_COLOR_SCHEME SITE BLUE
FND_COLOR_SCHEME USER JOHNDOE BLUE
FND_COLOR_SCHEME USER JANEDOE BLUE

Apache was bounced, and forms launched fine without this issue.

Query for CPU patches

Here's my updated query to identify the CPU patches (April 2011 - October 2012) applied on your 11i or R12 instance:

column BUG format a8;

column PATCH format a60;
set linesize 100;
set pagesize 200;

select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
14321237, 'Oct 2012 CPU Patch for R12.1 + ATG_PF.B.Delta3',
14321239, 'Oct 2012 CPU Patch for R12.0.6',
14321240, 'Oct 2012 CPU Patch for 11i+RUP7',
14321241, 'Oct 2012 CPU Patch for 11i+RUP6',
13979374, 'July 2012 CPU patch for 11i+RUP7',
13979377, 'July 2012 CPU patch for 11i+RUP6',
13979372, 'July 2012 CPU patch for R12.1+ATG_PF.B.Delta3',
13979375, 'July 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621942, 'April 2012 CPU for R12.1+ATG_PF.B.Delta2',
13621941, 'April 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621940, 'April 2012 CPU for 11i+RUP7',
13621939, 'April 2012 CPU for 11i+RUP6',
13322561, 'Jan 2012 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Jan 2012 CPU for R12.0+ATG_PF.A.Delta6',
13322559, 'Jan 2012 CPU for 11i+RUP7',
13322557, 'Jan 2012 CPU for 11i+RUP6',
12794417, 'Oct 2011 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Oct 2011 CPU for R12.0+ATG_PF.B.Delta6',
12794415, 'Oct 2011 CPU for 11i+RUP7',
12794414, 'Oct 2011 CPU for 11i+RUP6',
12406916, 'Jul 2011 CPU for R12.1',
12406915, 'Jul 2011 CPU for R12.0',
12406914, 'Jul 2011 CPU for 11i+RUP7',
12406913, 'Jul 2011 CPU for 11i+RUP6',
11660357, 'Apr 2011 CPU for R12.1',
11660356, 'Apr 2011 CPU for R12.0',
11660355, 'Apr 2011 CPU for 11i+RUP7',
11660354, 'Apr 2011 CPU for 11i+RUP6'
) PATCH
from APPS.AD_BUGS b
where b.BUG_NUMBER in
('14321237','14321239','14321240','14321240',
'13979374','13979377','13979372','13979375',
'13621942','13621941','13621940','13621939',
'13322561','12794416','13322559','13322557',
'12794417','12794416','12794415','12794414',
'12406916','12406915','12406914','12406913',
'11660357''11660356','11660355','11660354')
order by patch;