topic Re: how to execute a PLSQL block in Talend in Talend Studio

how to execute a PLSQL block in Talend

Anonymous — Wed, 18 Oct 2017 20:58:33 GMT

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;"

Re: how to execute a PLSQL block in Talend

TRF — Wed, 18 Oct 2017 22:01:56 GMT

Hi,
What is the error message?
What if you try the same PL/SQL block using SQL*Plus?

Re: how to execute a PLSQL block in Talend

Anonymous — Thu, 19 Oct 2017 14:52:53 GMT

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;"

Re: how to execute a PLSQL block in Talend

cterenzi — Thu, 19 Oct 2017 15:21:53 GMT

Is that the right syntax for including single quotes in the middle of a string? I don't have an Oracle system to test on, but I thought it should look something like:
( queue_name => ''TESTQ'', enqueue_options...);

Re: how to execute a PLSQL block in Talend

Anonymous — Thu, 19 Oct 2017 16:42:03 GMT

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