CREATE PROCEDURE( p_cur OUT a_cur)
IS
type rec is record( a varchar2(2), b number, c number);
type tab is table of rec;
tab1 tab:=tab();
begin
tab1.extend;
tab1(tab1.last).a:='as';
tab1(tab1.last).b:=2;
tab1(tab1.last).c:=3;
tab1.extend;
tab1(tab1.last).a:='jj';
tab1(tab1.last).b:=2;
tab1(tab1.last).c:=3;
--??---
end;
I have created a nested table here tab1 ,but my issue is that i want to use this nested table in a cursor and want to return whole records using this nested table ,limitation is that i dont want to use any temporary table . I am using RDBMS as ORACLE
If you want to use a collection as if it were a table then you'll nedd to look a the
TABLE()
function:There is an example here: http://www.dobosz.at/oracle/select-from-plsql-table/
And another good resource here: http://www.databasejournal.com/features/oracle/article.php/2222781/Returning-Rows-Through-a-Table-Function-in-Oracle.htm
You'll need to declare the collection type in the database before then populating it in your procedure and then selecting from it.
I answered a question using this method here: Can a table variable be used in a select statement where clause?
Take a look as it should help you with what you are trying to achieve.
Hope it helps...
EDIT: In response to your question this code should do what you want it to. I haven't tested it but it should be very close to what you need and you can debug it if needed.
Type Created
Type Created
N.B.: This is code amended from this page: http://www.akadia.com/services/ora_return_result_set.html