Home » SQL & PL/SQL » SQL & PL/SQL » select statement in collections
select statement in collections [message #403251] Thu, 14 May 2009 07:57 Go to next message
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member

I have a below pl/sql block which creates collection of record in my procedure not in database.

can i select with some conditions in where clause on that collections
can i insert/update on that collections.

In that Begin End; block i want to do the above process

can i if so explain with some examples for the above.


  cursor c1 is select id,name,age,sex from emp;
  Type typ_emp is table of c1%rowtype;
  typemp typ_emp;




Re: select statement in collections [message #403279 is a reply to message #403251] Thu, 14 May 2009 10:19 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member

If you want to issue select statements that query on collections, the collection type must be a database type and you must use the CASTS / MULTISET / TABLE operators in the right comibinations.

In some situations (pipelined functions for example), the type not need be defined beforehand in the database but only in a package specification. However, in reality the database defines "placeholder" types quietly behind the scenese anyway so there is still a type defined in the database, you simply cannot reference it directly.

You type however is neither. It is defined on a cursor and this is not a "nested table" kind of collection so you are stuck. You cannot construct a query that makes use of the variable based on the cursor type.

This is my best knowledge. Of courase I have been wrong before. Maybe someone smarter than me can give an example and prove me wrong. However, I have never seen it, do not see it in documentation, and have never done it.

Good luck, Kevin
Previous Topic: query long time execution
Next Topic: PARALLEL query with ROWNUM
Goto Forum:

Current Time: Sat Oct 22 20:23:48 CDT 2016

Total time taken to generate the page: 0.14393 seconds