title: How to Copy data from table with a nested table column
body: <p>I have a table that has one of its columns as a nested table.</p>
  
  <p>I want to copy data of this table to another. How do we frame the INSERT clause for this seems challenging:</p>
  
  <p>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".</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>Obviously below fails: (with a ORA-00904: : invalid identifier)</p>
  
  <pre><code>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 ;
  </code></pre>
  
  <p>So i try:</p>
  
  <pre><code>   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)** ;
  </code></pre>
  
  <p>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. </p>
  
  <p>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.</p>