I have tried to use a cursor inside a sql query. This works:

SELECT 1, CURSOR (SELECT 1 FROM DUAL) 
    FROM DUAL;

But when I'm trying to use this subquery inside a with statement. It doesn't work anymore.

WITH t (a, b) AS (
    SELECT 1, CURSOR (SELECT 1 FROM DUAL) 
    FROM DUAL)
SELECT t.a
  FROM t;

[Error] Execution (5: 10): ORA-00900: invalid SQL statement

Why?

CodePudding user response：

From the CURSOR expression documentation:

Restrictions on CURSOR Expressions

The following restrictions apply to CURSOR expressions:

• If the enclosing statement is not a SELECT statement, then nested cursors can appear only as REF CURSOR arguments of a procedure.
• If the enclosing statement is a SELECT statement, then nested cursors can also appear in the outermost select list of the query specification or in the outermost select list of another nested cursor.
• Nested cursors cannot appear in views.
• You cannot perform BIND and EXECUTE operations on nested cursors.

Unfortunately, the phrasing is unclear as the "can appear only" from the first bullet point is expanded upon in the second bullet point and it may have been clearer phrasing to have said:

• If the enclosing statement is a SELECT statement, then nested cursors can also appear ONLY in the outermost select list of the query specification or in the outermost select list of another nested cursor.

If you want you can wrap the CURSOR expression in a PL/SQL stored function to convert it to a collection data type (or perform some other operation and return a scalar).

For example, defining the function:

CREATE FUNCTION pipe(
  i_cur IN SYS_REFCURSOR
) RETURN SYS.ODCINUMBERLIST PIPELINED
IS
  v_value PLS_INTEGER;
BEGIN
  LOOP
    FETCH i_cur INTO v_value;
    EXIT WHEN i_cur%NOTFOUND;
    PIPE ROW (v_value);
  END LOOP;
  
  CLOSE i_cur;
END;
/

Then:

WITH t (a, b) AS (
  SELECT 1,
         pipe(CURSOR(SELECT 1 FROM DUAL))
  FROM   DUAL
)
SELECT t.a
FROM   t;

Works but removing the function call gives your error.

You could also use:

WITH FUNCTION sum_cursor(i_cur IN SYS_REFCURSOR) RETURN NUMBER
IS
  v_total NUMBER := 0;
  v_value NUMBER;
BEGIN
  LOOP
    FETCH i_cur INTO v_value;
    EXIT WHEN i_cur%NOTFOUND;
    IF v_value IS NOT NULL THEN
      v_total := v_total   v_value;
    END IF;
  END LOOP;
  
  CLOSE i_cur;
  
  RETURN v_total;
END;
t (a, b) AS (
  SELECT 1,
         sum_cursor(CURSOR(SELECT 2 FROM DUAL))
  FROM   DUAL
)
SELECT t.*
FROM   t;

Which outputs:

A	B
1	2

db<>fiddle here