One Star

execute statement in tOracleRow

I need to update a materialized view via a statement like this:
EXECUTE DBMS_MVIEW.REFRESH( 'MV_NAME', 'C' )
Putting this directly in a tOracleRow component yields:
Exception in component tOracleRow_1
java.sql.SQLException: ORA-00900: invalid SQL statement

Wrapping it in a BEGIN/END as suggested in early forum posts yields:
Exception in component tOracleRow_1
java.sql.SQLException: ORA-06550: line 1, column 16:
PLS-00103: Encountered the symbol "DBMS_MVIEW" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "DBMS_MVIEW" to continue.

Can anyone suggest how to proceed?
Thanks!
2 REPLIES
One Star

Re: execute statement in tOracleRow

While using tOracleRow try the following statement
CALL DBMS_MVIEW.REFRESH('MV_NAME', 'C')

Since you are executing a procedure, you can also use tOracleSP.
Regards,
Abhi
One Star

Re: execute statement in tOracleRow

Thank you iterator for the suggestion.
I checked with one of our DBAs and he suggested removing the 'EXECUTE' from the SQL. The query in the tOracleRow looks like this:
BEGIN
dbms_mview.refresh( 'MV_BUILDING_KEYWORDS', 'C' );
dbms_mview.refresh( 'MV_PRIMARY_KEYWORDS', 'C' );
dbms_mview.refresh( 'MV_REGIONAL_KEYWORDS', 'C' );
END;

Which when activated by a tFixedFlowInput works as desired.