| How do I query a dynamic table type plsql [message #39295] |
Wed, 03 July 2002 12:38  |
Graham
Messages: 9
Registered: March 2002
|
Junior Member |
|
|
I can create a package with a
type record, type ref cursor and type table
and I can load the dynamic table - about 80 records.
How do I query the dynamic table I have loaded?
I tried to
open cursor
for select c1
from dynamic_table
where ...
and got the error
PLS-00201:identifier 'dynamic_table' must be declared
All ideas much appreciated
Thx
|
|
|
|
| Re: How do I query a dynamic table type plsql [message #39296 is a reply to message #39295] |
Wed, 03 July 2002 14:54  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You cannot select from it the way you can with a "normal" table. You will not be able to return values from a pl/sql table through a ref cursor.
Two options that I can think of:
1) Loop through the pl/sql table, load the qualifying rows (those that meet your criteria) into a second pl/sql table, and return that second pl/sql table as an output parameter (not as a ref cursor though).
2) Loop through the pl/sql table, load the qualifying rows into a global temporary table and then return the contents of the GTT through the ref cursor.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
