Oracle JDBC throws ORA-00933 with FETCH NEXT n ROWS ONLY

Question

When I query with

SELECT EQUIP_TYPE, DESCRIP
FROM EQUIP_TYPE
ORDER BY EQUIP_TYPE

I got results OK. But when I query with

SELECT EQUIP_TYPE, DESCRIP
FROM EQUIP_TYPE
ORDER BY EQUIP_TYPE
FETCH NEXT 10 ROWS ONLY

there was an SQLException:

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

Query: SELECT EQUIP_TYPE, DESCRIP FROM EQUIP_TYPE ORDER BY EQUIP_TYPE FETCH NEXT 10 ROWS ONLY Parameters: []

Now EQUIP_TYPE type is known to be unique, there are totally 367 rows in the table.

My environment is Ubuntu 18.04, Java 1.8, ojdbc6-11.2.0.2.0.jar and I work in a Tomcat container with Datasource set up. The Oracle DB is in a remote host.

That works, thanks! But how to skip the first n row? I tried SELECT EQUIP_TYPE, DESCRIP FROM EQUIP_TYPE WHERE rownum>2 AND rownum <= 10 ORDER BY EQUIP_TYPE which give me no results, and no exception — cpliu338, Oct 03 '19 at 05:40
Please refer , https://stackoverflow.com/questions/29894645/how-to-skip-the-first-n-rows-in-sql-query/29894850 — Shameela A, Oct 03 '19 at 06:12

score 1 · Answer 1 · answered Oct 03 '19 at 06:45

FETCH and OFFSET are introduced in Oracle 12c. They can be used like the following: The OFFSET clause specifies the number of rows to skip before the row limiting using FETCH starts

SELECT
    EQUIP_TYPE,
    DESCRIP
FROM
    EQUIP_TYPE
ORDER BY
    EQUIP_TYPE
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

For getting the same result in Oracle version prior to 12c, You can use ROW_NUMBER analytical function as following:

SELECT
    EQUIP_TYPE,
    DESCRIP FROM
(SELECT
    EQUIP_TYPE,
    DESCRIP,
    ROW_NUMBER() OVER (ORDER BY EQUIP_TYPE) RN
FROM
    EQUIP_TYPE)
WHERE RN BETWEEN 11 AND 20;

Cheers!!

score 0 · Answer 2 · answered Oct 03 '19 at 05:57

You might be running a version of Oracle that does not support the FETCH clause (it was introduced in Oracle 12c only).

As an alternative, you can use ROWNUM:

SELECT *
FROM (SELECT EQUIP_TYPE, DESCRIP FROM EQUIP_TYPE ORDER BY EQUIP_TYPE) x
WHERE ROWNUM <= 10

Oracle JDBC throws ORA-00933 with FETCH NEXT n ROWS ONLY

2 Answers2