CODEWITHSUNDEEP
oracle-databaseinsertnested-table
user2275460
user2275460

Reputation: 339

How to Copy data from table with a nested table column

I have a table that has one of its columns as a nested table.

I want to copy data of this table to another. How do we frame the INSERT clause for this seems challenging:

Consider. The field that is a nested table is phone_list whose type is a user defined type "TBL_PHONE_EXTN" which is a table of "typ_phone_extn".

CREATE OR REPLACE TYPE typ_phone_extn AS OBJECT
(phone_number VARCHAR2 (20), extension VARCHAR2 (10));
/

CREATE OR REPLACE TYPE tbl_phone_extn AS TABLE OF typ_phone_extn;
/

Obviously below fails: (with a ORA-00904: : invalid identifier)

INSERT INTO sch2.sub_pat_address (
          pat_address_id,
          pat_id,
          **phone_list,**
          last_updated_by
)
   SELECT pat_address_id,
          pat_id,
          **phone_list,**
           last_updated_by
     FROM sch1.sub_pat_address ;

So i try:

   SELECT pat_address_id,
          pat_id,
           **tbl_phone_extn(typ_phone_extn (phone_number,extension)),**
           last_updated_by
     FROM sch1.sub_pat_address, **table(phone_list)** ;

What this does is unnest the nested table. So i end up with more records than i want - meaning if a specific pat_address_id had a phone_list of 5 phone,extn combination this gives me 5 records that i cannot and should not be inserting.

So question is, how to keep the nest (nested table column) as-is and insert into the new table? Well, CTAS may be one option but that requires a whole new table instead of a INSERT. Any help will be greatly appreciated.

Upvotes: 1

Views: 2132

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You can use the COLLECT function to reassemble the unnested elements into a nested table, casting that back to your actual collection type:

   SELECT pat_address_id,
          pat_id,
           cast(collect(typ_phone_extn(phone_number,extension)) as tbl_phone_extn),
           last_updated_by
     FROM sch1.sub_pat_address, table(phone_list)
 GROUP BY pat_address_id, pat_id, last_updated_by;

And you can then use that for your insert, obviously.

The only reason I can see you'd have a problem with your original simple insert would be if each schema had its own types and their tables were built using their own types. But then you'd get ORA-00932: inconsistent datatypes or ORA-01031: insufficient privileges rather than ORA-00904.

Even if you have privileges on the types across the schemas, Oracle UDTs have to be the exact same type - it's not enough for them to be constructed identically. If they are different entries in ALL_OBJECTS then they are not interchangeable.

Upvotes: 1

Related Questions