Returning a Ref Cursor based on a PL/SQL Collection

Lucas Jellema 2
0 0
Read Time:1 Minute, 16 Second

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.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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?

Virtual 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 […]
%d bloggers like this: