0 
0 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.
Related posts:
Oracle Quiz on SQL and PL/SQL – See Water Burning – paper for ODTUG 2005 Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases 
Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table 
Oracle Open World / Java One Preview bij AMIS
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.
Nice code snippet. I don’t know why Oracle has restrictions on the Cast so that you can’t use types declared in packages…