Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to execute a plsql block using tOracleRow component as shown below. But it doesn't like that. Could anyone help me with this? Thanks
"BEGIN
EXECUTE IMMEDIATE 'declare
l_enqueue_options dbms_aq.enqueue_options_t;
l_message_properties dbms_aq.message_properties_t;
l_message sys.aq$_jms_text_message;
.....
....
....
commit;'
end;"
Thanks for your response. Yes, the plsql code works in SQLPLUS. Here is the error I get:
Exception in component tOracleRow_1
java.sql.SQLException: ORA-06550: line 2, column 19:
PLS-00382: expression is of wrong type
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:936)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:298)
[statistics] disconnected
Here is my test plsql code used in tOracleRow:
"BEGIN
EXECUTE IMMEDIATE 'declare
l_enqueue_options dbms_aq.enqueue_options_t;
l_message_properties dbms_aq.message_properties_t;
l_message sys.aq$_jms_text_message;
l_msgid raw(16);
begin
l_message := sys.aq$_jms_text_message.construct;
l_message.set_text(xmltype('+'<emp><ename>Test</ename></emp>'+').getClobVal());
dbms_aq.enqueue
( queue_name => '+'TESTQ' +
' , enqueue_options => l_enqueue_options
, message_properties => l_message_properties
, payload => l_message
, msgid => l_msgid
);
commit;
end';
end;"
Thanks for the reply.
The same code works fine in SQLPLUS with single quote (e.g. 'AQ_STEP_INBOUND_PV'). Talend doesn't like double single quotes (e.g. ''AQ_STEP_INBOUND_PV''). So I tried with three single quotes (e.g. '''AQ_STEP_INBOUND_PV'''), but I get the same runtime error:
Exception in component tOracleRow_1
java.sql.SQLException: ORA-06550: line 2, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)