topic Re: execute statement in tOracleRow in Talend Studio

execute statement in tOracleRow

Anonymous — Sat, 16 Nov 2024 13:09:32 GMT

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!

Re: execute statement in tOracleRow

Anonymous — Wed, 05 Jan 2011 03:30:49 GMT

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

Re: execute statement in tOracleRow

Anonymous — Wed, 05 Jan 2011 17:17:52 GMT

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.