ORA-00933: SQL command not properly ended on jdbc call

Question

I have this query:

SELECT col FROM table WHERE   
last_updated > SYS_EXTRACT_UTC(systimestamp) - INTERVAL ?  DAY TO SECOND(1)

and in my db access code,

stmt.setString(1, "0 00:01:30.0");//stmt is OraclePreparedStatement
rs = stmt.executeQuery();//results in exception

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

If I replace the bind and run the query in Sql Developer, it runs without error. Where am I wrong with the java code?

Add a semicolon (;) at the end of the SELECT statement and see if that helps. — , Aug 25 '16 at 12:57
@mathguy: no that would make thins worse: http://stackoverflow.com/q/10728377/330315 — , Aug 25 '16 at 13:02
Unfortunately you can't pass the value for an interval as a parameter like that. — , Aug 25 '16 at 13:04
@a_horse_with_no_name - Thank you! Indeed, till five seconds ago (more by the time I finish typing), I was among the many who thought the ; is a statement terminator in Oracle SQL. Now I know better. (I just looked at the SQL\*Plus manual quickly, and if you know that ';' is not part of SQL, you can see it's consistent with what the manual says; but if you don't know it already, beforehand, it's harder to understand that from how the manual is written.) — , Aug 25 '16 at 13:15
@mathguy: well `;` **is** the statement terminator in SQL - and is used by SQL clients to identify the individual statements. But in JDBC you only send **single** statements so you may not include the `;` with the query string. This is however a limitation of the Oracle JDBC driver (or backend) - other driver or DBMS _do_ allow to send the `;` — , Aug 25 '16 at 13:21
@a_horse_with_no_name - From the way you describe it it's a separator rather than terminator. I always prefer terminators over separators (life would be much simpler with comma-terminated strings rather than comma-separated, for example.), but it seems SQL uses a separator. — , Aug 25 '16 at 13:33
@mathguy It is a statement terminator in commandline clients as it signals "this statement is complete", if you have a script consisting of a single statement, you would (usually) still need it. In that way it is not a separator. — Mark Rotteveel, Aug 25 '16 at 19:30
@MarkRotteveel - I just looked around, and it appears the SQL Standard is not freely available. I wanted to see what it says. If Wikipedia is to be believed, the Standard defines ; as the statement terminator. If that is true, this has nothing to do with commandline clients, it is a requirement of the standard (regardless of what an implementing vendor chooses to do in their product). I was confused by a Comment in the thread a_horse_... linked to; that Comment was just plain wrong. I got it now. I understand also that the Oracle JDBC driver is non-compliant in this regard. — , Aug 25 '16 at 19:53
@mathguy In the SQL standard, statements themselves have no terminator. There are some context where a terminator is required: there is an 'SQL terminator' (which includes the semicolon, but also others) for embedded SQL (SQL embedded in C, etc), the semicolon is also used in 'direct invocation of sql', which seems to refers commandline-like execution, there are also some block-like statements that may contain other statements which must end with a semicolon. The normal execution mode of JDBC however is dynamic SQL, where semicolons or other terminators are absent. — Mark Rotteveel, Aug 25 '16 at 20:05
@mathguy So semicolons don't belong, but a lot of databases (or their drivers) are lenient so they strip them out, or sometimes even allow execution of multiple statements that way. — Mark Rotteveel, Aug 25 '16 at 20:07
I found a draft of the 92 standard... there is no mention of "statement terminator" for the language itself. There is only a SQL terminator for **embedded** SQL. Exactly as you said the first time. The terminator doesn't have to be ; through - it may be END-EXEC, semicolon or right parenthesis. So the Oracle JDBC driver may be compliant even without semicolon... (And that is the old standard, and only a draft to boot...) :-) Thank you! — , Aug 25 '16 at 20:23

SubOptimal · Answer 1 · 2016-08-26T06:02:24.000

The problem is that INTERVAL '0 00:01:30.0' DAY TO SECOND(1) is a literal of datatype INTERVAL. So you cannot use a positional parameter inside it.

You have to use one of the convertion functions NUMTOYMINTERVAL or NUMTODSINTERVAL.

The amended SQL for the prepared statement would be

SELECT col FROM table WHERE   
last_updated > SYS_EXTRACT_UTC(systimestamp) - NUMTODSINTERVAL(?,'SECOND')

and the parameter needs to be set as

stmt.setInt(1, 90);

edit As Alex mentioned himself. Another way

SELECT col FROM table WHERE   
last_updated > SYS_EXTRACT_UTC(systimestamp) - TO_DSINTERVAL(?)

and the parameter can be set as string

stmt.setString("0 00:01:30.0");

A manual check that it's the same interval

select NUMTODSINTERVAL(90,'SECOND') as "NUMTODSINTERVAL", 
       INTERVAL '0 00:01:30.0' DAY TO SECOND(1) as "INTERVAL",
       TO_DSINTERVAL('0 00:01:30.0') as "TO_DSINTERVAL"
from dual

output

NUMTODSINTERVAL     INTERVAL            TO_DSINTERVAL      
------------------- ------------------- -------------------
+00 00:01:30.000000 +00 00:01:30.000000 +00 00:01:30.000000

But now you have to convert the interval string to a number; why not use `to_dsinterval()` which takes the existing string value directly? — Alex Poole, Aug 25 '16 at 19:41
@AlexPoole You can. It depends how the interval is available in you code. I found a string representation not very common. ;-) I added your example to my answer. — SubOptimal, Aug 26 '16 at 06:03

score 2 · Answer 2 · answered Aug 25 '16 at 13:59

As @a_horse_with_no_name said in a comment, you can't pass an interval value as a parameter. That form is an interval literal, and you can't use a bind variable for the literal part.

You can change your query to use the to_dsinterval() function instead:

SELECT col FROM table WHERE   
last_updated > SYS_EXTRACT_UTC(systimestamp) - TO_DSINTERVAL(?)

Quick demo with SQL*Plus/SQL Developer bind variables:

var val varchar2(30);
exec :val := '0 00:01:30.0';

The equivalent of your query with a CTE to provide dummy data:

with t(last_updated, col) as (select systimestamp, 1 from dual)
SELECT col FROM t WHERE   
last_updated > SYS_EXTRACT_UTC(systimestamp) - INTERVAL :val  DAY TO SECOND(1);

SQL Error: ORA-00933: SQL command not properly ended

With the function instead:

with t(last_updated, col) as (select systimestamp, 1 from dual)
SELECT col FROM t WHERE   
last_updated > SYS_EXTRACT_UTC(systimestamp) - TO_DSINTERVAL(:val);

       COL
----------
         1

ORA-00933: SQL command not properly ended on jdbc call

2 Answers2