Как скопировать данные из таблицы с вложенным столбцом таблицы

У меня есть таблица, в которой один из столбцов является вложенной таблицей.

Я хочу скопировать данные этой таблицы в другую. Как мы сформулируем предложение INSERT для этого, кажется сложным:

Учитывать. Поле, являющееся вложенной таблицей, называется phone_list, тип которого определяется пользователем как "TBL_PHONE_EXTN", который является таблицей "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;
/

Очевидно, что ниже происходит сбой: (с ORA-00904:: недопустимый идентификатор)

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 ;

Итак, я пытаюсь:

   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)** ;

Что это делает, так это распаковывает вложенную таблицу. Таким образом, я получаю больше записей, чем хочу - это означает, что если конкретный pat_address_id имел phone_list из 5 комбинаций phone, extn, это дает мне 5 записей, которые я не могу и не должен вставлять.

Итак, вопрос в том, как сохранить гнездо (столбец вложенной таблицы) как есть и вставить его в новую таблицу? Что ж, CTAS может быть одним из вариантов, но для этого требуется совершенно новая таблица вместо INSERT. Любая помощь будет оценена.


oracle insert nested-table
person user2275460    schedule 10.10.2014    source источник
comment
Как были созданы ваши таблицы? INSERT INTO ... tbl_col SELECT tbl_col ... должен работать без ошибок. Или я упустил какие-то тонкости?   -  person Sylvain Leroux    schedule 10.10.2014
comment
Вы уверены, что получили ORA-00904, а не ORA-00932: inconsistent datatypes или ORA-01031: insufficient privileges? Таблицы в обеих схемах построены со ссылкой на свой собственный тип объекта/таблицы или общий?   -  person Alex Poole    schedule 10.10.2014

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете использовать COLLECT функцию для повторной сборки невложенные элементы во вложенную таблицу, возвращая ее к вашему фактическому типу коллекции:

   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;

И вы можете использовать это для своей вставки, очевидно.

Единственная причина, по которой я вижу, что у вас возникла бы проблема с исходной простой вставкой, заключалась бы в том, что каждая схема имела бы свои собственные типы, а их таблицы были построены с использованием своих собственных типов. Но тогда вы получите ORA-00932: inconsistent datatypes или ORA-01031: insufficient privileges, а не ORA-00904.

Даже если у вас есть привилегии для типов в схемах, UDT Oracle должны быть полностью одного и того же типа — недостаточно, чтобы они были сконструированы одинаково. Если это разные записи в ALL_OBJECTS, то они не взаимозаменяемы.

person Alex Poole    schedule 10.10.2014