SQLSyntaxErrorException: ORA-00900: invalid SQL statement using Java

Question

I need to use following via Java EXEC DBMS_STREAMS_ADM.SET_TAG(tag => HEXTORAW('17')); using a simple Database client. along with other usual select/delete queries but its complaining for invalid SQL statement.

I tried removing the exec as its for PL/SQL and calling it with {} but still I am getting the same error.

• rgettman: How are you calling the stored procedure in Java? Please include the code you have.
• Sandeep: DBClient.execute("exec DBMS_STREAMS_ADM.set_tag(tag => HEXTORAW('17'))")
• Mick Mnemonic: EXEC is SQL*Plus syntax and you can't use it here. You could use an anonymous PL/SQL block or the CALL syntax instead.

Answer 1

EXEC is SQL*Plus (Oracle's native SQL client) syntax and you can't use it here. The JDBC syntax for calling stored procedures is through the CALL directive. You can omit the parameter name in the call (tag => HEXTORAW('17')). For example:

try (CallableStatement cs 
 = myConnection.prepareCall("{ call DBMS_STREAMS_ADM.SET_TAG(HEXTORAW(?)) }")) {
 cs.setString(1, "17"); 
 cs.execute();
}

It is unclear which DBClient you are currently using (please tell us), but the following might work as well:

DBClient.execute("{ call DBMS_STREAMS_ADM.set_tag(HEXTORAW('17')) }");

or then

DBClient.execute("begin DBMS_STREAMS_ADM.SET_TAG(HEXTORAW('17')); end;");

• Sandeep: I tried DBClient.execute("{ call DBMS_STREAMS_ADM.set_tag(tag => HEXTORAW('17')) }"); but its complaning for DBMS_STREAMS_ADM must be declared
• Sandeep: It works out now. Some user permission issue at the end. Thanks