databaseoracle-databaseplsqloracle19cdatabase-cursor

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


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.


Solution

  • 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.