Workflow background process errors after 19c upgrade

[satish]

Dear Erman,

We have upgraded 12c EBS database to 19c. Workflow background process erroring out with below error.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

PLSQL concurrent programs are running fine. Issue not with UTL FILE DIR.

In test instance,the program completing normal. We verified and found there is additional event set in production


Production:
========

SQL> show parameter event; NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      10995 trace name context forev
                                                 er, level 16, 10946 trace name
                                                  context forever, level 845414
                                                 4


TEST:
=====

SQL> show parameter event; NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      10946 trace name context forev
                                                 er, level 8454144


Can you please gudie what can be done?

Thanks,
Satish

[ErmanArslansOracleBlog]

We have that event instructed to be set in "Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)" --> To enable the UTL_FILE functionality on which the supplemental UTL_FILE_DIR parameter depends, ensure that you have set the following event at the CDB level in your database initialization parameters: event='10946 trace name context forever, level 8454144'

According to what you have sent, you have that event set in your PROD instance, which is the instance having the issues. What about that other event (10995), why is it there?  -- it is already removed from the EBS 12- 19C interoperability document --> Removed setting event 10995 trace name context forever, level 16.

and there is no ref. for 12.2, it was there for 12.1 and it was removed then..

Additional info:

ORA-10995: general event for materialized views

Cause: N/A
Action: This internal event is used to enable various actions associated with MVs. The event level is interpreted as a bitmap of actions to enable.//*Comment: Not for general purpose use.LEVEL ACTION//---------------------------------------------------------------------------0x0001 enable callback-based refresh0x0002 invalidate dependents for online redef0x0004 allow PCT fast refresh after CONSIDER FRESH


1)---> so this  10995 is set in your PROD, but not set in your TEST.. so what about removing it ? -- Workflow background process is a process that can encounter ORA-01555.. So that means it uses the Materialized views in the backend.. So that event set for the MW access may affect it.

2)Also sent me the workflow background process log, if the issue persists.

3)Also see -> 11.i - 12 How to Resolve the Most Common Workflow Background Engine Problems (Doc ID 466535.1) -- finding the cause / the table /query related with that ORA-06502 can help..

Some other refs.

How to Monitor the FNDWFBG Workflow Background Program? (Doc ID 369537.1)
How To Generate Performance Trace for Workflow Background Process? (Doc ID 734425.1) -- that is for performance but can also be used for getting the trace and capturing the SQLs(to get the problematic SQL )

[satish]

Dear Erman,

Thanks for the update.

We have removed the event.

SQL> sho parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string      10946 trace name context forev
                                                 er, level 8454144


As advised, We have enabled trace and found below sql statement.

The following statement encountered a error during parse:

UPDATE WF_ITEM_ACTIVITY_STATUSES SET ACTIVITY_STATUS = :B8 , ACTIVITY_RESULT_CODE = :B7 , ERROR_NAME = :B6 , ERROR_MESSAGE = :B5 , ERROR_STACK = :B4 WHERE ITEM_TYPE = :B3 AND ITEM_KEY = :B2 AND PROCESS_ACTIVITY = :B1

Error encountered: ORA-06502
********************************************************************************



Log File:
=====

+---------------------------------------------------------------------------+
Application Object Library: Version : 12.2
Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.
FNDWFBG: Workflow Background Process
+---------------------------------------------------------------------------+
Current system time is 08-FEB-2023 11:26:24
+---------------------------------------------------------------------------+

**Starts**08-FEB-2023 11:26:24
**Ends**08-FEB-2023 11:26:25
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
No completion options were requested.

Output file size:
0

Output is not being printed because:
The print option has been disabled for this report.
Deleting empty output file.

SQL> select count(*) from apps.WF_ITEM_ACTIVITY_STATUSES;

  COUNT(*)
----------
  44070140


With same number of records,the program completes normal in test instance.

Please advice.

Thanks,
Satish

[ErmanArslansOracleBlog]

*What are the values of those bind variables?
--you can gather it from an sql trace (gathered with binds)

*Also please check the target rows using an SQL like the following and investigate the data/and the related workflows with your functional admin.

select * from apps.WF_ITEM_ACTIVITY_STATUSES WHERE ITEM_TYPE = :B3 AND ITEM_KEY = :B2 AND PROCESS_ACTIVITY = :B1   --replace the bind values with the bind values of the query...

some additional resources: Diagram of Relationship between Core Workflow Tables (Doc ID 444446.1)

*Are there any other workflow errors that may be related with this one..? --Check these types of things with your functional admin also. ORA-06502 may also be a result (not a cause..). Maybe something related with the workflow (workflow activities) is failing there, and effects that update and makes that update get ORA-06502 due to having improper values in one of its bind variables. -- WF_ITEM_ACTIVITY_STATUSES is used to store the related records that the workflow activities generate.. (as it name suggests --> ITEM ACTIVITY STATUS)