Unable to use FETCH OFFSET syntax with inner CURSOR query in Oracle SQL

Question

When attempting to query a resultset with an offset and a limit with the OFFSET x ROWS FETCH y ROWS ONLY syntax outside of the inner cursor

DECLARE
  l_hits SYS_REFCURSOR;
BEGIN 
  OPEN l_hits for
    SELECT CURSOR(SELECT *
                    FROM emp) hits
      FROM dept
     ORDER BY deptno 
    OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
END;

results in the following error...

ERROR at line 1:
ORA-22902: CURSOR expression not allowed
ORA-06512: at line 4

Removing OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY solves it but also removes functionality.

The problem is that the goal is to offset the top level block rather than the CURSOR. How can I accomplish this?

Thank you.

Joao Pereira · Accepted Answer · 2025-02-05 18:17:45Z

1

Solved it. Ended up being pretty straightforward. Need to do the OFFSET x ROWS FETCH NEXT y ROWS ONLY in an inline view.

DECLARE
  l_hits SYS_REFCURSOR;
BEGIN 
  OPEN l_hits for
    SELECT CURSOR(SELECT *
                    FROM emp) hits
      FROM (SELECT * 
              FROM dept
             ORDER BY deptno
            OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) dept;
END;

Hopefully it is useful for others in the future.

answered Feb 5 at 18:17

Joao Pereira

6036 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Unable to use FETCH OFFSET syntax with inner CURSOR query in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related