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

2 Responses to The long and winding road II

  1. dombrooks says:

    Scott Martin pointed out that in the above example, from 9i onwards the custom open_cursor can be replaced by the built-in CURSOR() directive.

    Thanks Scott.

    I think this is the fault of my example and my description of what it was trying to achieve – the open_cursor() bit is really just a placeholder for a stored proc that already exists somewhere which you want to reuse and merge with another one – which was a dodgy / artificial requirement in the first place leading to my misleading example. My lack of comments does not help though.

  2. dombrooks says:

    Going back to the original premise – think java developer going to far with re-use in database code and ignoring the most efficient set operations to do something.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: