Home » SQL & PL/SQL » SQL & PL/SQL » Table type collection in SELECT (Oracle 11g)
Table type collection in SELECT [message #684726] Thu, 05 August 2021 03:46 Go to next message
sinpeak
Messages: 57
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.
Re: Table type collection in SELECT [message #684748 is a reply to message #684726] Wed, 11 August 2021 00:58 Go to previous messageGo to next message
_jum
Messages: 576
Registered: February 2008
Senior Member
Do you mean something like:
SELECT CAST (COLLECT((objtype1(child_id))) AS tabtype1) tt 
  FROM child_tab 
 WHERE parent_id IN (1,2); 

[Updated on: Wed, 11 August 2021 00:58]

Report message to a moderator

Re: Table type collection in SELECT [message #684749 is a reply to message #684748] Wed, 11 August 2021 01:20 Go to previous message
sinpeak
Messages: 57
Registered: January 2011
Location: india
Member
Yes, thanks a lot. !
Previous Topic: BMP not correctly formated with UTF8 Characterset
Next Topic: Creating rows every 5 minutes without overlap
Goto Forum:
  


Current Time: Sat Nov 27 05:02:31 CST 2021