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

santosh jk
  • 111
  • 1
  • 11
  • 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. – Alex Poole Jun 28 '21 at 15:13
  • Im using 11g. Could you please suggest some alternative to this query in 11g – santosh jk Jun 28 '21 at 15:41
  • 1
    Does this answer your question? [Alternatives to LIMIT and OFFSET for paging in Oracle](https://stackoverflow.com/questions/2912144/alternatives-to-limit-and-offset-for-paging-in-oracle) – astentx Jun 28 '21 at 16:12

0 Answers0