Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-12008: error in materialized view refresh path

greatwhiteJan 21 2020 — edited Jan 21 2020

I hope I am putting this in the right place and I am new to this site, so I will apologize now if I make a mistake.

I appreciate any insight anyone can give me.

ORA-12008: error in materialized view refresh path

I have several Materialized Views which I can compile them and refresh them as well as select data from them.

However when we run our ETL we get the error below. The ETL process calls our Cloud Package which the first thing it does or attempts is refreshes the views, but instead throws the below error.

I have looked at the ORA-01722 can not find what that would be, even if our ETL made it to SQL statements they are just plain select statements.

Any hints as to what it might be?

This is the error Message as portion of the Package that is first called is further down

D:\disclosure_portal_etl\Java>java automateExport Prod pdata initial

  1. java.sql.SQLException: ORA-12008: error in materialized view refresh path

ORA-01722: invalid number

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017

ORA-06512: at "EE.DP_CLOUD_PKGE", line 14

ORA-06512: at line 1

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)

        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)

        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)

        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

        at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:205)

        at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1043)

        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)

        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)

        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)

        at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)

        at Dbase.exportDataToJason(Dbase.java:166)

        at automateExport.main(automateExport.java:33)

D:\disclosure_portal_etl\Java>cd /d "D:\disclosure_portal_etl\dt-1.7"

This is where it fails in the beginning of the package I believe at DBMS_MVIEW.REFRESH('MV_DP_CLOUD_DATA');

BEGIN

DBMS_MVIEW.REFRESH('MV_AGENTS', 'F');

DBMS_MVIEW.REFRESH('MV_EVALUATORS', 'F');

DBMS_MVIEW.REFRESH('MV_DP_EE_EVALUATIONS', 'F');

DBMS_MVIEW.REFRESH('MV_DP_NH_EVALUATIONS', 'F');

DBMS_MVIEW.REFRESH('MV_DP_NHP_EVALUATIONS', 'F');

--DBMS_MVIEW.REFRESH('MV_DP_CLOUD_DATA');

END RefreshSnapshots;

PROCEDURE RefreshInitialData IS

/*

Purpose: Refresh the mv_dp_cloud_data only at the initial migration.

*/          

BEGIN

DBMS_MVIEW.REFRESH('MV_DP_CLOUD_DATA');   --I think this is where it fails

END RefreshInitialData;

Comments

Post Details

Added on Jan 21 2020
1 comment
113 views