Error In Procedure EXECUTE_PROFILES PLS-00905: object DEMANTRA.ROLLING_UPDATES is invalid (Doc ID 1493065.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Demantra Demand Management - Version 7.3.1 and later
Information in this document applies to any platform.

Symptoms

Customer is on 731

Per customer they had updated the Baseline Forecast series server expression in Production instance to hide the forecast in past months.
Server Expression: sum(decode(sign( branch_data.sales_date - to_date(get_max_date,'mm/dd/yyyy hh24:mi:ss')),-1, NULL ,0, NULL ,nvl(branch_data.sim_val_1,#FORE@0#*1)*#UNIT#))

They are now running Execute Profiles from within a workflow for rolling data. The workflow fails to execute. When looking at DB_Exception_Log they see the error;

ERROR:
Error description 'Database internal error:ORA-20002: -6550 ORA-06550:
line 1, column 7:
PLS-00905: object DEMANTRA.ROLLING_UPDATES is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored Error in PROCEDURE EXECUTE_PROFILES line 2664

dynamic_ddl( 'BEGIN ROLLING_UPDATES; END;') Problem with the procedure EXECUTE_PROFILES !!

Also the procedure, Rolling_Updates, becomes invalid.

The collaborator logs shows


2012-07-24 14:41:18,503 EDT WFProcess_2 FATAL workflow.general: ORA-20002: -6550 ORA-06550: line 1, column 7: PLS-00905: object DEMANTRA.ROLLING_UPDATES is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored Error in PROCEDURE EXECUTE_PROFILES line 2664 dynamic_ddl( 'BEGIN ROLLING_UPDATES; END;') ORA-06512: at "DEMANTRA.SEND_ERR_MESSAGE", line 21 ORA-06512: at "DEMANTRA.DYNAMIC_DDL", line 22 ORA-06512: at "DEMANTRA.EXECUTE_PROFILES", line 2664 ORA-06512: at line 1 java.sql.SQLException: ORA-20002: -6550 ORA-06550: line 1, column 7: PLS-00905: object DEMANTRA.ROLLING_UPDATES is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored Error in PROCEDURE EXECUTE_PROFILES line 2664 dynamic_ddl( 'BEGIN ROLLING_UPDATES; END;') ORA-06512: at "DEMANTRA.SEND_ERR_MESSAGE", line 21 ORA-06512: at "DEMANTRA.DYNAMIC_DDL", line 22 ORA-06512: at "DEMANTRA.EXECUTE_PROFILES", line 2664 ORA-06512: at line 1 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169) at com.demantra.workflow.step.StoredProcedureStep.runStoredProcedure(StoredProcedureStep.java:517) at com.demantra.workflow.step.StoredProcedureStep.doExecute(StoredProcedureStep.java:227) at com.demantra.workflow.step.LinkedStep.execute(LinkedStep.java:920) at com.demantra.workflow.process.WFProcess.mainLoop(WFProcess.java:1305) at com.demantra.workflow.process.WFProcess.run(WFProcess.java:703) at java.lang.Thread.run(Thread.java:595) 2012-07-24 14:41:18,507 EDT WFProcess_2 ERROR dpweb.general: #################################### Step Failure. #################################### 2012-07-24 14:41:18,507 EDT WFProcess_2 ERROR dpweb.general: Process ID: 782048 Schema ID: 1535 Schema name: 'Lag Forecast' Step ID: 'Step1' Step name: 'lag_forecast' Error description 'Database internal error:ORA-20002: -6550 ORA-06550: line 1, column 7:' 2012-07-24 14:41:18,507 EDT WFProcess_2 ERROR dpweb.general:

When they try to compile the Rolling_updates, they get the following Compilation error

Project: sqldev.temp:/IdeConnections%23NB-DP04.jpr
Procedure DEMANTRA.ROLLING_UPDATES@NB-DP04
Error(215,94): PLS-00103: Encountered the symbol "MM" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem or != or ~= >= and or like like2 like4 likec between || multiset member submultiset The symbol "*" was substituted for "MM" to continue.
Error(215,105): PLS-00103: Encountered the symbol "HH24" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem or != or ~= >= and or like like2 like4 likec between || member submultiset
Error(215,109): PLS-00049: bad bind variable 'MI'
Error(215,112): PLS-00049: bad bind variable 'SS'

Customer then did the following

Edit the ROLLING_UPDATES to ''mm/dd/yyyy hh24:mi:ss'' in place of 'mm/dd/yyyy hh24:mi:ss' in the code.
Compiled the same
Ran the same manually, it ran fine

They then ran it again from the workflow it failed to execute step and gave the same error. When they look at the procedure again it has Error and these are the same error messages they saw earlier. The ''mm/dd/yyyy hh24:mi:ss'' gets changed back to 'mm/dd/yyyy hh24:mi:ss' in the code.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms