Table type collection in SELECT [message #684726] |
Thu, 05 August 2021 03:46 |
|
sinpeak
Messages: 59 Registered: January 2011 Location: india
|
Member |
|
|
Hello,
create table parent_tab (parent_id number(9));
create table child_tab (child_id number(9),parent_id number(9));
create or replace type objtype1 AS object (child_id NUMBER(9) )
CREATE OR REPLACE TYPE tabtype1 AS TABLE OF objtype1;
A java based UI back end code need to fire an SQL SELECT to get the data of parent and its children for a given list of parents.
But due to some constraints they need the data to be selected as something like this -
parent_id
and
table type collection of all children of parent ID
I know that this can be done inside a BEGIN END block. Something like this -
DECLARE
v_table tabtype1;
BEGIN
SELECT objtype1 (child_id)
BULK COLLECT INTO v_table
FROM parent_tab WHERE PARENT_ID = <list of parent IDs>;
END;
But due to some constraint this is not what the Java UI code team would do.
They need to directly fire an SQL (without a BEGIN-END block) and get rows out of a SELECT such that each row has :-
(Parent_ID , Table type object of collection of all child IDs linked to the parent_id)
Please advise if this can be done in a SELECT without using BEGIN-END block.
Thanks.
|
|
|
|
|