CODEWITHSUNDEEP
oracle-databasetypes
user2789772
user2789772

Reputation: 51

Working with custom types within a stored procedure

I have a custom type:

CREATE OR REPLACE TYPE my_type IS OBJECT
(
    field_one number,
    field_two varchar2(10),
);

and a nested table of this type:

CREATE TYPE my_type_nt AS TABLE OF my_type;

and another custom type that contains this nested table:

CREATE OR REPLACE TYPE parent IS OBJECT
(
    field_one number,
    field_two my_type_nt,
);

I have to query a table for parent objects and then depending on the PK for that record, query another table for the all my_type objects for that parent.

so something like:

-- i know following code is wrong
select * into parent1
from table1 
where table1.column1 = something;

and then:

for every record in parent1
    populate it's my_type_nt
    for every record in my_type_nt
        do something
    end loop
end loop

My questions are: 1. Is my approach wrong? Should I be joining the two tables instead? 2. I will have to populate the parent types anyway (this stored proc feeds into another stored proc which has the parent type as an input. What is an efficient way to select data into the parent type?

Upvotes: 0

Views: 105

Answers (1)

APC
APC

Reputation: 146179

We can populate a nested table with a sub-query using CAST() and MULTISET()

select parent(p.id,
   cast(multiset(select c.c_id
                        , c.c_name
                 from c
                 where c.p_id = p.id)
   as my_type_nt)
   )
into local_par
from p
where p.id = param_id;

Whether this is the best approach for you depends on what actual processing you have obfuscated in your pseudo-code: populate it's my_type_nt ... do something

"What is an efficient way to select data into the parent type?"

If you want to process multiple parents you should create a nested table type for it too:

CREATE TYPE parent_nt AS TABLE OF parent_t;
/

Then you can populate it with the BULK COLLECT syntax:

select parent(p.id,
   cast(multiset(select c.c_id
                        , c.c_name
                 from c
                 where c.p_id = p.id)
   as my_type_nt)
   )
bulk collect into local_par_nt
from p
where p.id <= param_id;

Then loop through the collection to process each parent

for idx in 1 .. local_par_nt.count()
loop
    do_something;

Upvotes: 1

Related Questions