How to use a nested table in a cursor

Question

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

i want to use something like ...select a,b,c from tab1 in cursor to return cursor to front end; — gaurav, Dec 13 '11 at 07:19

score 4 · Accepted Answer · edited May 23 '17 at 12:28

4

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.

-- Create the relevent Object
CREATE TYPE data_obj_type AS OBJECT (
   a VARCHAR2(2),
   b NUMBER,
   c NUMBER
);

Type Created

-- Create the collection to hold the objects
CREATE TYPE table_obj_type IS TABLE OF data_obj_type;

Type Created

CREATE OR REPLACE
PROCEDURE cursor_values(
   p_cur OUT sys_refcursor
)
IS
   -- Create a variable and initialise it
   tab1 table_obj_type := table_obj_type();
BEGIN
   -- Populate the tab1 collection
   tab1.extend;
   tab1(tab1.last) := data_obj_type('as', 2, 3);
   tab1.extend;
   tab1(tab1.last) := data_obj_type('jj', 2, 3);
   --
   -- Open ref_cursor for output
   OPEN p_cur FOR
      SELECT a,
             b,
             c
        FROM TABLE(CAST(tab1 AS table_obj_type));
END cursor_values;

N.B.: This is code amended from this page: http://www.akadia.com/services/ora_return_result_set.html

edited May 23 '17 at 12:28

Community

1
1

answered Dec 13 '11 at 09:51

Ollie

17,058
7
48
59

:http://stackoverflow.com/questions/8489520/ora-22905-cannot-access-rows-from-a-non-nested-table-item – gaurav Dec 13 '11 at 12:55
:Please see code i have written in the link above ,please find whats wrong with my code – gaurav Dec 13 '11 at 12:56
Wow, the code in that question looks VERY overcomplicated. This question says that you simply want to select from a collection and return them in a cursor. Is this still the case? – Ollie Dec 13 '11 at 13:28
yes the code is overcomplicated ,but i am not able to resolve the error dat i am getting while executing the code.please help me out ,and yes its the same case where i want to select a collection into a cursor – gaurav Dec 13 '11 at 15:08
OK, see the edit to my answer for some code to do what you need. – Ollie Dec 13 '11 at 15:36
BTW, you needn't have asked the other question you have linked, as the answer to this will satisfy both questions. – Ollie Dec 13 '11 at 15:42
I am getting the same error : PL/SQL: ORA-22905: cannot access rows from a non-nested table ite m – gaurav Dec 13 '11 at 16:18
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/5821/discussion-between-gaurav-and-ollie) – gaurav Dec 13 '11 at 16:22
My mistake, I CAST to the wrong TYPE. I have now corrected the code in my answer, the FROM clause should now read: FROM TABLE(CAST(tab1 AS table_obj_type)) instead of: FROM TABLE(CAST(tab1 AS data_obj_type)); – Ollie Dec 13 '11 at 16:36
:Thanks your are just awsome ,thanks for the good solution and your patience – gaurav Dec 13 '11 at 16:41
No worries, glad I could help. You should probably delete the other question associated with this otherwise someone else might spend their time trying to answer it. – Ollie Dec 13 '11 at 17:07
:I have one more question ,object cannot be declared in spec of package ,now suppose the object type that we have globally created ,i want that to be record type and used that inside the package ,Is that possible ._I have tried creating a record type in package specification and using the same as we used in the above code ,but that is giving me error_ .**I think creating a global type is restriction to this approach**,Is there any possible solution for putting these type inside a package – gaurav Dec 15 '11 at 20:04
If you have a globally declared type, just use variables of that type in your package. You've no need to declare a new type within your package. – Ollie Dec 15 '11 at 22:12
I dont want global type ,i want to everything within package – gaurav Dec 16 '11 at 09:52
Then you are artificially restricting your available options. You should probably as the question as a standalone question on SO. – Ollie Dec 16 '11 at 11:00
I am not restricting my available options ollie, Our database does not allow us priviledge to create global object type,thats why i asked you the question.Can i post the question again in SO? – gaurav Dec 16 '11 at 15:55
I'd post a new question, specifically mentioning the scenario where you are unable to create global types and the solution must be confined within a package. You'll probably have to use a pipelined function to do what you want to do but if you post it in another question then anyone searching for pipelined functions in future will be able to see the solution. – Ollie Dec 17 '11 at 12:00
:Ok I do a proof of this concept(POC) and then i post the question ,earlier i have implemented pipeline function ,so i try first and then post ,thanks – gaurav Dec 17 '11 at 13:35

How to use a nested table in a cursor

1 Answers1

Linked