ResultSet rset = stmt.executeQuery ("select count(*) from Invoices;"); // note there is a semicolon at the end of SQL statement
Question: why does having a semicolon at the end of the SQL statement in JDBC (1.6, 1.7) for Oracle 11g database generates an error such as below:
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
Once I remove the semicolon, the code works fine.
I always thought that ending all SQL statements with a semicolon was part of SQL standard and Oracle PL/SQL certaintly supports and encourages semicolons (and requires it in many cases).
So why does JDBC object to semicolons ?
Is there a JDBC Oracle driver setting/property that can be set to =true/=1 such that I can use semicolons at end of SQL statements ?
JDBS doesn't object at all. JDBC doesn't care.
JDBC sent that string as-is to the DBMS.
It's the DBMS that hates it.
The semicolon is part of SQL-PLUS/Pl-SQL syntax, where appropriate.
In this case SQL-PLUS would have stripped the semicolon off before
sending the SQL to the DBMS. If you sent real PL-SQL, such as:
= "create or replace PROCEDURE SP_TestDate(pdmydate IN OUT Date)"
+ " IS err_num NUMBER; "
+ " BEGIN pdmydate := null; "
+ " select mydate into pdmydate from mytesttable; "
+ " EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; "
+ " END SP_TestDate;";
The semicolons would be sent unquestioned as before, but would be accepted by the DBMS.
why does having a semicolon at the end of the SQL statement in JDBC (1.6, 1.7) for Oracle 11g database generates an error
The query statement terminator can be database specific so the JDBC driver will send any terminator needed by the DB.