CODEWITHSUNDEEP
oracle-databaseplsql
Dev Ngron
Dev Ngron

Reputation: 135

How to fetch a ref_cursor into Oracle's WITH CLAUSE

Is it possible to use a ref_cursor with ORACLE WITH CLAUSE. For example, I have the following scenario. First I have a procedure which returns a ref_cursor

PROCEDURE p_return_cursor(p_id IN NUMBER, io_cursor OUT t_cursor) 
AS
BEGIN
  OPEN io_cursor FOR
    SELECT col1, col2 
      FROM Table1 t 
     WHERE t.id = p_id;
END;

Second, I have another procedure on which I make a call to p_return_cursor:

PROCEDURE p_test(p_cid IN NUMBER)
AS
  l_cursor t_cursor;
  l_rec Table1%ROWTYPE;
BEGIN
  p_return_cursor(p_id => p_cid, io_cursor => l_cursor);
  -- CODE GOES HERE
...

Now, my question is, can I make a temp table using the Oracle's WITH CLAUSE using the cursor; something like:

...
  WITH data AS (
    LOOP
      FETCH l_cursor INTO l_rec;
      EXIT WHEN l_cursor%NOTFOUND;
        SELECT l_rec.col1, l_rec.col2 FROM DUAL;
    END LOOP;
    CLOSE l_cursor;
    )

Upvotes: 0

Views: 2278

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17944

You cannot do it directly. You can, however, BULK COLLECT your cursor into a PL/SQL table variable and use that in a WITH clause.

Be careful of memory usage if the cursor contains many rows.

Full example:

CREATE TABLE table1 ( col1 NUMBER, col2 NUMBER );

INSERT INTO table1 ( col1, col2 ) SELECT rownum, 100+rownum FROM DUAL CONNECT BY ROWNUM <= 15;

COMMIT;

CREATE OR REPLACE PACKAGE se_example AS

  TYPE t_cursor IS REF CURSOR
    RETURN table1%ROWTYPE;

  TYPE l_rec_tab IS TABLE OF table1%ROWTYPE;


  PROCEDURE p_test (p_cid IN NUMBER);
END se_example;



CREATE OR REPLACE PACKAGE BODY se_example AS
  -- private
  PROCEDURE p_return_cursor (p_id IN NUMBER, io_cursor OUT t_cursor) AS
  BEGIN
    OPEN io_cursor FOR
      SELECT col1,
             col2
      FROM   table1 t; 
      --WHERE  t.id = p_id;  -- I didn't put "id" column in my sample table, sorry...
  END p_return_cursor;

  PROCEDURE p_test (p_cid IN NUMBER) IS
    l_cursor   t_cursor;
    l_tab      l_rec_tab;
    l_dummy    NUMBER;
  BEGIN
    p_return_cursor (p_id => p_cid, io_cursor => l_cursor);

    FETCH l_cursor BULK   COLLECT INTO l_tab;

  -- *** instead of this    

  --  WITH data AS (
  --    LOOP
  --      FETCH l_cursor INTO l_rec;
  --      EXIT WHEN l_cursor%NOTFOUND;
  --        SELECT l_rec.col1, l_rec.col2 FROM DUAL;
  --    END LOOP;
  --    CLOSE l_cursor;
  --    )
  --    '
  --

  -- *** do this    

    WITH data AS 
      ( SELECT col1, col2 FROM TABLE(l_tab) )
    SELECT sum(col1 * col2) INTO l_dummy
    FROM data;

    dbms_output.put_line('result is ' || l_dummy);

  END p_test;
END se_example;


begin
  se_example.p_test(100);
end;

Upvotes: 2

Related Questions