Returning a Ref Cursor based on a PL/SQL Collection

Lucas Jellema 2

The question on the OTN Forum was: How do i add rows to a pl/sql table and then use the table to open a ref cursor.. Somehow I was a little intrigued and decided to try to formulate a reply. The first part of the question, although asked in a somewhat unclear way, was obvious. The second part was more interesting. What I came up with – using the TABLE operator – was the following:
First create a type in the database that describes the collection that we want to open the ref cursor for:

create type num_tbl is table of number
/

Then create a function that will define and populate a nested table of this type, open a ref cursor for it and return that ‘handle’:

create or replace
function get_num_cursor
return sys_refcursor
is
  l_num_tbl num_tbl;
  l_idx integer;
  l_refcursor sys_refcursor;
begin
  l_num_tbl:= num_tbl(10,20);
  l_num_tbl.extend(5);
  l_num_tbl(l_num_tbl.last):= 30;
  l_num_tbl.extend;
  l_num_tbl(l_num_tbl.last):= 40;
  open l_refcursor for select * from table(cast (l_num_tbl as num_tbl));
  return l_refcursor;
end;
/

Selecting this function from dual in SQL*Plus shows the following result:

SQL> select get_num_cursor from dual
  2  /

GET_NUM_CURSOR
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

COLUMN_VALUE
------------
          10
          20




          30
          40

8 rows selected.

The next step is to make proper use this ref cursor in the client programs.

2 thoughts on “Returning a Ref Cursor based on a PL/SQL Collection

  1. Do you know how to do the same thing but with a collection which uses a local (PL/SQL) type definition? I get the error ORA-00902: invalid datatype. If I try without the cast operator (which worked just fine also with an SQL type) then I get PLS-00642: local collection types not allowed in SQL statements.

  2. Nice code snippet. I don’t know why Oracle has restrictions on the Cast so that you can’t use types declared in packages…

Comments are closed.

Next Post

Another Pop-Quiz: Whose VPD policy is used when executing SQL in a (definer rights) package?

Facebook0TwitterLinkedinVirtual Private Database does not let me go. Today I had another burning question to which I could not see the immediate logical answer. I know that when a Policy has been set up for a table and I execute a query against that table the Policy Function will return […]