CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11gsqlplus
Vicky
Vicky

Reputation: 5540

Ignoring SQLPLUS commands in JDBC

I have a .sql file that contains below statements:

SET LINESIZE 2000
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
SET PAGESIZE 0
SET HEADING ON
SET FEEDBACK OFF
SET VERIFY OFF

INSERT INTO TABLE_A
--get some value from TABLE B that will be added in Table A....

COMMIT;

EXIT;

When I run this SQL in my SQL Editor (TOAD/SQL Navigator etc.) , it works fine. I see some messages though when SQLNavigator execute this command:

SQL*Plus command ignored.
Processing ...
WHENEVER SQLERROR EXIT 1 ROLLBACK

SQL*Plus command ignored.
Processing ...
WHENEVER OSERROR EXIT 1 ROLLBACK

SQL*Plus command ignored.
Processing ...
SET PAGESIZE 0

When I run this SQL through JDBC, i get an exception:

Caused by: java.sql.SQLException: ORA-00922: missing or invalid option

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)

I'm assuming this error is because JDBC is not able to understand SQLPlus statements and failing. Is there a way I can tell JDBC to ignore those statements and just run the main SQL? Or to resolve this I just need to modify the SQL file by removing the SQLPlus statements?

Upvotes: 1

Views: 2453

Answers (2)

Justin Cave
Justin Cave

Reputation: 231791

You'll need to remove the SQL*Plus commands from the file.

JDBC is intended to be a database-agnostic interface so it is designed to work the same with a variety of different database engines. SQL*Plus commands are designed to work just with SQL*Plus connecting to an Oracle database (though other tools that support Oracle databases will often support a subset of SQL*Plus commands as well). It wouldn't make sense for JDBC to know about what constitutes a SQL*Plus command so it has no way to figure out what is a SQL*Plus command or to filter them out.

Beyond that, simply removing the SQL*Plus commands will change the semantics of the script. The WHENEVER SQLERROR and WHENEVER OSEROR commands instruct SQL*Plus to issue a rollback in the event of an error (which it sounds like your script generates). You'd need to code that logic into your JDBC application if you want to match the behavior.

Upvotes: 1

muratgu
muratgu

Reputation: 7311

When you run these statements through TOAD, etc. they usually parse the text either by a delimiter (e.g. semi-column) or by line feed and run the statements separately. However, when you send the whole text to JDBC, it's probably trying to run it all at once, hence the error. You may have to parse the statements and separate them properly before sending to JDBC.

Upvotes: 0

Related Questions