cto_pln_pct_his Value Is Lost After Applied Patch:22275972 For Parallel DBMS_SCHEDULER (Doc ID 2795097.1)

Last updated on OCTOBER 11, 2022

Applies to:

Symptoms

On : 12.2.8 version, Workflow,Production Environment

ACTUAL BEHAVIOR
---------------
Customer has 12.2.8 version.
When attempting execute CTO explosion in Demantra some of the parallel
DBMS_SCHEDULER jobs fail with below mentioned errors:
ERROR
-----------------------
"ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1127
ORA-06512: at "DP.PARALLEL_MANAGER", line 1073
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1118
ORA-06512: at "DP.CTO_CALC", line 1058
ORA-29471: DBMS_SQL access denied

The workflow does not fail. This makes the explosion functionality unstable
because sometimes not all parent demands are exploded correctly down to
children, as a workaround customer has to re-execute the explosion workflow,
however job failure is seen consistent but number of failed jobs vary between
3-4.
Using 12.2.8 build 18 SP 0 and 19c Database. Using standard CTO functionality
of Oracle Demantra.

Recently customer upgraded to 19c

From Bug:
Bug 22275972 - CTO PLANNING PERCENTAGE HISTORY NOT CALCULATING ACCURATELY
Patch: 22275972 is provided.

Provided Patch: 22275972 ( 12240174-ARU #21223263 12.2.x ) to customer

Customer applied the patch and tested, "cto_pln_pct_his" value is lost.

These lines are commented from the CTO patch:
Commented section:
--WHERE total_demand IS NOT NULL
--AND total_demand > 0

causing "cto_pln_pct_his" value is lost
 

EXPECTED BEHAVIOR
-----------------------
causing "cto_pln_pct_his" value has to be retained after applying Patch:22275972

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Log in to Demantra Workflow manager
2. Execute CTO explosion
3. some of the parallel DBMS_SCHEDULER jobs fail
4. Patch: 22275972 is applied to resolve the above.
5. Noticed lines are commented from the CTO patch:
Commented section:
--WHERE total_demand IS NOT NULL
--AND total_demand > 0

causing "cto_pln_pct_his" value is lost

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.