0 
private static final String  GET_OBJECT_WITH_PREFIX =
    " SELECT OBJECT_KEY, OBJECT_VALUE, LAST_UPDATE_DATE from TEST_TABLE "
        + " where OBJECT_KEY like ? "
        + " order by LAST_UPDATE_DATE DESC "
        + " offset ? rows fetch next ? rows only ";

  prefix = prefix + "%";
  pst = conn.prepareStatement(GET_OBJECT_WITH_PREFIX);
  pst.setString(1, prefix);
  pst.setInt(2, offset);
  pst.setInt(3, fetchSize);
  System.out.println("prepared statement: "+pst.toString());
  rs = pst.executeQuery();

Above code snippet throws below exception

Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended [exec] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462) [exec] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405) [exec] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931) [exec] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481) [exec] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205) [exec] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548) [exec] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [exec] at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217) [exec] at java.lang.Thread.run(Thread.java:745) [exec] at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947) [exec] at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283) [exec] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441) [exec] at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769) [exec] at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823) [exec] at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671) [exec] at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:135)

Not sure where it went wrong I tried to print prepared statement as query to understand issue but its not priniting in systemout . Could some one help on the issue.

Thanks in advance

Improve this question
3
  • Which version of Oracle are you connecting to? The offset/fetch syntax wasn't introduced until 12c. Also not sure if the number of rows can be set with bind variable; but that would be a different error. Commented Jun 28, 2021 at 15:13
  • Im using 11g. Could you please suggest some alternative to this query in 11g Commented Jun 28, 2021 at 15:41
  • 1
    Does this answer your question? Alternatives to LIMIT and OFFSET for paging in Oracle Commented Jun 28, 2021 at 16:12
Related questions
0
PreparedStatement error
17
"ORA-01008: not all variables bound" error
1
Oracle error "SQL command not properly ended" (ORA-00933) for Java prepared statement
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.