Error while firing SQL command from JDBC - java.sql.SQLSyntaxErrorException

Question

I have a sql query which I fire on sql plus and I get the result. The same query when I fire from java code - jdbc I get an exception -

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended.

The query is

select prod_id, prod_area, prod_name, step_name,
       to_char(entry_time, 'DD/MM/YYYY HH24:MI:SS') as entry_time,
       round(extract(day from current_timestamp - entry_time)*1440 +
             extract(hour from current_timestamp - entry_time)*60 +
             extract(minute from current_timestamp - entry_time)) as pending_minutes
from prodreport
where exit_time is null;

java code

String query = "select prod_id,prod_area,prod_name,step_name,to_char(entry_time, 'DD/MM/YYYY HH24:MI:SS') as entry_time, round(extract(day from current_timestamp - entry_time)*1440 + extract(hour from current_timestamp - entry_time)*60 + extract(minute from current_timestamp - entry_time)) as pending_minutes from prodreport where exit_time is null;"

What happens if you remove the trailing ; from the query string? — jarlh
– jarlh, Commented May 22, 2019 at 7:51
please provide your java code. PreparedStatement indeed splits the SQL and removes semicolons — ItFreak
– ItFreak, Commented May 22, 2019 at 8:18

Mak · Accepted Answer · 2019-06-11 03:41:07Z

-1

Possibly its because of semicolon(;) which you are sending with query. Please go through the usage of it, It will might help.

The Semicolon

The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon.

Usage

There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

answered Jun 11, 2019 at 3:41

Mak

1,0768 silver badges20 bronze badges

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

1 Comment

user330315 Over a year ago

Why do you quote T-SQL rules when the question is about Oracle?

Collectives™ on Stack Overflow

Error while firing SQL command from JDBC - java.sql.SQLSyntaxErrorException

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related