| need select query based on pl sql table [message #639016] |
Mon, 29 June 2015 13:05  |
 |
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
i need to select data from pl sql table as cursor..is there any way ..i have written code as below
drop table child_table;
drop table parent_table;
/
create table parent_table(pk number primary key);
create table child_table(pk number primary key, fk REFERENCES parent_table(pk));
/
insert into parent_table (pk) values (1);
insert into parent_table (pk) values (2);
insert into child_table (pk, fk) values (11, 1);
insert into child_table (pk, fk) values (21, 1);
insert into child_table (pk, fk) values (32, 2);
/
declare
type rec is record
(
parent parent_table%rowtype,
child child_table%rowtype
);
type tbl is table of rec;
v_table tbl := tbl();
begin
-- this works
select * bulk collect into v_tbl from child_table;
open rct1 for select * from table(v_tbl);
end;
/
it is not working for me...
|
|
|
|
|
|
| Re: need select query based on pl sql table [message #639293 is a reply to message #639017] |
Sun, 05 July 2015 00:48  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
There is no direct way. You have to use pl/sql to manually add each pl/sql array row to a collection type by walking them, and then cast the collection type to a table in your cursor spec. There are examples of this in the Oracle doces and various web pages.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
