The long and winding road II

So, maybe some tests later, but for now a bit of code because I love writing code.

Let’s say you were crazy enough to want to, or for some bizarre reason you had to, write some code that took two separate ref cursors and merged them, how might you do that?

In 10g, you could use a pipelined function, kind of like this:

CREATE OR REPLACE PACKAGE fetch_cursor_pkg
AS
  --
  SUBTYPE mytyp IS all_objects%ROWTYPE;
  TYPE mytab IS TABLE OF mytyp;
  --
  FUNCTION open_cursor (
    i_sql IN LONG
  )
  RETURN SYS_REFCURSOR;
  --
  FUNCTION fetch_cursor (
    i_cursor  IN  SYS_REFCURSOR
  )
  RETURN fetch_cursor_pkg.mytab
  PIPELINED;
  --
  PROCEDURE test (
   o_cursor OUT SYS_REFCURSOR
  );
  --
END fetch_cursor_pkg;


CREATE OR REPLACE PACKAGE BODY fetch_cursor_pkg
AS
  --
  FUNCTION open_cursor (
    i_sql IN LONG
  )
  RETURN SYS_REFCURSOR
  AS
    --
    v_cursor SYS_REFCURSOR;
    --
  BEGIN
    --
    OPEN v_cursor FOR i_sql;
    --
    RETURN v_cursor;
    --
  END open_cursor;
  --
  FUNCTION fetch_cursor (
    i_cursor  IN  SYS_REFCURSOR
  )
  RETURN fetch_cursor_pkg.mytab
  PIPELINED
  AS
    --
    v_row mytyp;
    --
  BEGIN
 --
    LOOP
     --
  FETCH i_cursor INTO v_row;
     --
  EXIT WHEN i_cursor%NOTFOUND;
     --
     PIPE ROW (v_row);
     --
    END LOOP;
    --
 CLOSE i_cursor;
    --
 RETURN;
    --
  END;
  --
  PROCEDURE test (
   o_cursor OUT SYS_REFCURSOR
  )
  AS
  BEGIN
    --
    OPEN o_cursor FOR
         SELECT *
   FROM   TABLE(fetch_cursor_pkg.fetch_cursor(open_cursor('select a.* FROM all_objects a WHERE ROWNUM < 5')))
         UNION
         SELECT *
   FROM   TABLE(fetch_cursor_pkg.fetch_cursor(open_cursor('select a.* FROM all_objects a WHERE ROWNUM < 7')));
    --
  END;
  --
END fetch_cursor_pkg;
SQL> set lines 120 pages 999
SQL> var r refcursor
SQL> set autoprint on
SQL>  exec fetch_cursor_pkg.test(:r)


More

About these ads