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.

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.

• database
• oracle-database
• plsql
• oracle19c
• database-cursor

1 Answer 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.