ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error ORA-06512: at "APPS.WF_EVENT", line 3600

Akhilesh pinged me today and told me that autoconfig was not working on a newly upgraded R12 instance.  It was failing with these errors:

AutoConfig could not successfully execute the following scripts:
   Directory: /r12ascp/erpapp/10.1.3/perl/bin/perl -I /r12ascp/erpapp/10.1.3/perl/lib/5.8.3 -I /r12ascp/erpapp/10.1.3/perl/lib/site_perl/5.8.3 -I /r12ascp/erpapp/appl/au/12.0.0/perl -I /r12ascp/erpapp/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3/sun4-solaris-thread-multi /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/scripts/adexecsql.pl sqlfile=/r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install
     afwebprf.sql            INSTE8_PRF         1
     amscmprf.sql            INSTE8_PRF         1
     amswebprf.sql           INSTE8_PRF         1
     cncmprf.sql             INSTE8_PRF         1
     csfadmprf.sql           INSTE8_PRF         1
     oksfrmprf.sql           INSTE8_PRF         1

   Directory: /r12ascp/erpapp/inst/apps/r12ascp_r12ascp/admin/install
     ibywebprf.sh            INSTE8_PRF         1

If we tried running any of the above scripts, they failed with this error:

SQLPLUS Executable : /r12ascp/erpapp/10.1.2/bin/sqlplus


SQL*Plus: Release 10.1.0.5.0 - Production on Mon Feb 4 12:04:27 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "APPS.WF_EVENT", line 3600
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217

So I checked the code on line 3600 for wf_event package:

select line,text
from dba_source
where name='WF_EVENT'
and line between 3550 and 3610;

 3600
 wf_event.local_system_guid := hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
I did another code search:

select line,text
from dba_source
where text like '%WF_SYSTEM_GUID%';
This was the result of the query:
   select text into l_result from wf_resources where name='WF_SYSTEM_GUID' and
language='US';

SQL> select text from wf_resources where name='WF_SYSTEM_GUID' and
 2  language='US';

TEXT
--------------------------------------------------------------------------------
CAFECAFE-0013-0001-0029-ABCDEFABCDEF

That was weird as that is the GUID used by Oracle JRE plugin 1.3.1.29.

I checked the actual GUID:
SQL> select guid from wf_systems;

GUID
--------------------------------
D37180AE23A4479AE04400212846C6CE

Updated the correct GUID:

SQL> update wf_resources
 2  set text='D37180AE23A4479AE04400212846C6CE'
 3  where name='WF_SYSTEM_GUID' and
 4  language='US';

1 row updated.

SQL> commit;


Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID

New error this time:

SQL> @afwebprf.sql apps appspassword r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.WF_EVENT", line 3602
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217

Checked the code again:


    3602
 wf_event.local_system_status := wf_core.translate('WF_SYSTEM_STATUS');

SQL> select text from wf_resources where name='WF_SYSTEM_STATUS' and
 2  language='US';

TEXT
--------------------------------------------------------------------------------
CAFECAFE-0013-0001-0029-ABCDEFABCDEF

So I checked with query:

select count(*) from wf_resources where
text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';
1207 rows

select count(*) from wf_resources;
1775 rows



So I inquired about an existing good instance.  No R12 instance was available, so I copied from an 11i instance with sqlplus COPY command:

First put the tnsnames.ora entry for the good instance in the DB $TNS_ADMIN/tnsnames.ora file
Then used the sqlplus copy command, which is the fastest way to copy data between two oracle databases:

SQL> copy from apps/****@11iascp to apps/****@r12ascp create wf_resources_d1 using select * from wf_resources

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table WF_RESOURCES_11i created.

  1717 rows selected from apps@r12ascpdev.
  1717 rows inserted into WF_RESOURCES_11i.
  1717 rows committed into WF_RESOURCES_11i at apps@r12ascp.


Since there was a difference of 58 rows, I first copied those rows in a different table:

create table wf_resources_more as
( select * from wf_resources
where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);

Which created table wf_resources_more with 58 rows.

Then I delete those 58 rows from the wf_resources table:
delete wf_resources

where (name,type) in
(
select name,type from wf_resources
minus
select name,type from wf_resources_11i
);

Then I updated the table with 11i values, through a correlated update statement:

update wf_resources a
set a.text=(select text from wf_resources_11i b
where a.type||a.name = b.type||b.name)

That updated the 1717 common rows with correct values.

Then I inserted the extra 58 rows new in R12

insert into wf_resources (select * from wf_resources_more);
commit;

I ran adwebprf.sql again and it gave a new error:

SQL> @afwebprf.sql apps appspass r12ascp
Connected.
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "APPS.WF_EVENT", line 3604
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 3064
ORA-06512: at "APPS.FND_PROFILE", line 3514
ORA-06512: at "APPS.ADX_PRF_PKG", line 203
ORA-06512: at line 167
ORA-06512: at line 217

Again I checked code on line 3604 of WF_EVENT:

 select name into wf_event.local_system_name

     3605
 from wf_systems

     3606
 where guid = wf_event.local_system_guid;
Then I realized that after the correlated update, the wf_resources table now had the GUID of 11i system:

SQL> select name,text
 2  from wf_resources
 3  where name='WF_SYSTEM_GUID';

NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
WF_SYSTEM_GUID
CA43363B52162863E04400212846C6CE

So I updated it again:

I checked the actual GUID:
SQL> select guid from wf_systems;

GUID
--------------------------------
D37180AE23A4479AE04400212846C6CE

Updated the correct GUID:

SQL> update wf_resources
 2  set text='D37180AE23A4479AE04400212846C6CE'
 3  where name='WF_SYSTEM_GUID' and
 4  language='US';

1 row updated.

SQL> commit;


Re-ran $INST_TOP/admin/install/adwebprf.sql apps appspassword ORACLE_SID

SQL> @afwebprf.sql apps appsapss1 r12ascp
Connected.
[ APPS_WEB_AGENT ]
Application Id : 0
Profile Value  : http://r12ascp.justanexample.com:8004/pls/r12ascp
Level Name: SITE
INFO           : Updated/created profile option value.
.
Deleted : 0
[ APPS_SERVLET_AGENT ]
Application Id : 0
Profile Value  : http://r12ascp.justanexample.com:8004/OA_HTML
Level Name: SITE
INFO           : Updated/created profile option value.
.
Deleted : 0
[ APPS_JSP_AGENT ]
Application Id : 0
Profile Value  : http://r12ascp.justanexample.com:8004
Level Name: SITE
INFO           : Updated/created profile option value.
.
Deleted : 0
[ APPS_FRAMEWORK_AGENT ]
Application Id : 0
Profile Value  : http://r12ascp.justanexample.com:8004
Level Name: SITE
INFO           : Updated/created profile option value.
.
Deleted : 0
[ WF_MAIL_WEB_AGENT ]
Application Id : 0
Profile Value  :
Level Name: SITE
INFO           : Updated/created profile option value.
.
.
.

It succeeded.

I told Akhilesh about the result and asked him to run autoconfig.  We tailed the log at

$INST_TOP/admin/log/02042015/adconfig.log

Adconfig succeeded without errors this time:

AutoConfig is exiting with status 0

AutoConfig execution completed on Mon Feb  4 20:17:58 2013

Time taken for AutoConfig execution to complete : 2 mins  38 secs

I was overjoyed, and so was Akhilesh.  We still had 53 rows which had the value of text as:
CAFECAFE-0013-0001-0029-ABCDEFABCDEF

select count(*) from wf_resources
where text='CAFECAFE-0013-0001-0029-ABCDEFABCDEF';
53 rows.

We would investigate further, how this had happened.  I suspect human error.  Human error makes troubleshooting more interesting, just like human error makes chess more interesting.  If it was a computer playing chess, it would not make any mistakes and win the game.  It is the human errors which make chess and troubleshooting, an interesting pursuit.