select statement in collections [message #403251] |
Thu, 14 May 2009 07:57  |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
Hi,
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.
Declare
cursor c1 is select id,name,age,sex from emp;
Type typ_emp is table of c1%rowtype;
typemp typ_emp;
Begin
...
End;
|
|
|
Re: select statement in collections [message #403279 is a reply to message #403251] |
Thu, 14 May 2009 10:19  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
No
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
|
|
|