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.

How are you calling the stored procedure in Java? Please include the code you have. — rgettman
– rgettman, Commented Jun 6, 2018 at 21:11
DBClient.execute("exec DBMS_STREAMS_ADM.set_tag(tag => HEXTORAW('17'))"); — Sandeep
– Sandeep, Commented Jun 6, 2018 at 21:16
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. — Mick Mnemonic
– Mick Mnemonic, Commented Jun 6, 2018 at 21:20

Mick Mnemonic · Accepted Answer · 2018-06-07 04:55:54Z

2

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

edited Jun 7, 2018 at 4:55

answered Jun 6, 2018 at 21:42

Mick Mnemonic

7,9562 gold badges29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sandeep Over a year ago

I tried DBClient.execute("{ call DBMS_STREAMS_ADM.set_tag(tag => HEXTORAW('17')) }"); but its complaning for DBMS_STREAMS_ADM must be declared

Sandeep Over a year ago

It works out now. Some user permission issue at the end. Thanks

Collectives™ on Stack Overflow

SQLSyntaxErrorException: ORA-00900: invalid SQL statement using Java

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related