Oracle Execute Immediate с DDL и вложенной таблицей

У меня проблема с попыткой использовать оператор Execute Immediate, содержащий оператор CREATE TABLE и определенный пользователем тип таблицы. Я получаю сообщение об ошибке ORA-22905 в Oracle 11g.
Есть ли обходной путь для решения этой проблемы?

CREATE TYPE MY_TABLE_TYPE AS TABLE OF VARCHAR2(30);  
/  
DECLARE  
    MT MY_TABLE_TYPE;  
BEGIN  
    SELECT * BULK COLLECT INTO MT FROM DUAL;  
    -- Two steps  
    EXECUTE IMMEDIATE 'CREATE TABLE MY_TABLE1 (A VARCHAR2(30))';  
    EXECUTE IMMEDIATE 'INSERT INTO  MY_TABLE1    SELECT * FROM TABLE(:T)' USING MT; -- OK  
    -- One step  
    EXECUTE IMMEDIATE 'CREATE TABLE MY_TABLE2 AS SELECT * FROM TABLE(:T)' USING MT; -- ERROR ORA-22905     
END;

Настоящий код для SELECT * FROM TABLE(:T) является динамическим (имя основной таблицы является временным) и медленным. Вот почему я стараюсь не создавать таблицу в два этапа (как это делается с MY_TABLE1). Также с двумя шагами я не могу использовать SELECT *, но мне нужно указать все столбцы (переменное количество и более 100 столбцов).


oracle create-table plsql execute-immediate nested-table
person andreaplanet    schedule 12.03.2014    source источник
comment
Есть ли причина, по которой вы не можете использовать представление исходной таблицы вместо создания и заполнения временной таблицы?   -  person Bob Jarvis - Reinstate Monica    schedule 13.03.2014
comment
Этот код выполняется в задании, его выполнение может занять до 2 часов, при этом будет извлечено до 500 000 записей. Результат можно просмотреть сразу и несколько раз (срок действия пользовательского сеанса этих временных таблиц).   -  person andreaplanet    schedule 13.03.2014
comment
Итак, чтобы построить эту таблицу, перемещая данные повсюду, требуется два часа. Сколько времени требуется только для выполнения запроса без выделения памяти и т. д.?   -  person Bob Jarvis - Reinstate Monica    schedule 13.03.2014
comment
Выполнение SELECT может занять два часа, поскольку записи извлекаются из таблицы, содержащей более 300 миллионов записей. Среднее время выполнения составляет от 2 до 10 минут, иногда при больших значениях поиска требуется до 2 часов. Создание таблицы и вставка данных — относительно быстрый процесс (вопрос секунд).   -  person andreaplanet    schedule 13.03.2014
comment
Также с помощью представления я не смог связать переменные, это создает ошибку: ВЫПОЛНИТЬ НЕМЕДЛЕННО 'СОЗДАТЬ ПРЕДСТАВЛЕНИЕ MY_TABLE2 КАК ВЫБРАТЬ * ИЗ ТАБЛИЦЫ (: T)' ИСПОЛЬЗОВАНИЕ MT;   -  person andreaplanet    schedule 13.03.2014
comment
Связано: Почему я не могу использовать переменные связывания в операторах DDL/SCL в динамическом SQL?   -  person sampathsris    schedule 26.08.2014

Ответы (1)


arrow_upward
0
arrow_downward

Вероятно, есть способ полностью избежать этой проблемы. Пропустите массовый сбор и используйте простой CREATE TABLE MY_TABLE AS SELECT * FROM DUAL; Это может быть чрезмерным упрощением реальной логики сбора данных. Но почти всегда есть способ обойти массовый сбор и сохранить данные непосредственно в объекте, используя только SQL.

Если решение PL/SQL действительно необходимо, ошибки ORA-22905: cannot access rows from a non-nested table item можно избежать, создав тип объекта и создав таблицу на основе этого типа. Возможно, это не решит проблему производительности, но, по крайней мере, избавит вас от необходимости заново указывать все столбцы в таблице DDL.

CREATE TYPE MY_TABLE_OBJECT IS OBJECT
(
    A VARCHAR2(30)
);

CREATE TYPE MY_TABLE_TYPE2 AS TABLE OF VARCHAR2(30);

DECLARE  
    MT MY_TABLE_TYPE2;
BEGIN  
    SELECT * BULK COLLECT INTO MT FROM DUAL;  
    EXECUTE IMMEDIATE 'CREATE TABLE MY_TABLE2 OF MY_TABLE_OBJECT';
    EXECUTE IMMEDIATE 'INSERT INTO  MY_TABLE2 SELECT * FROM TABLE(:T)' USING MT;
END;
/
person Jon Heller    schedule 13.03.2014
comment
Вы все еще используете два шага (как в моем первом примере, который работает). В реальном коде MY_TABLE1/2 имеет переменное количество более 100 столбцов, а SELECT представляет собой соединение между большой таблицей и TABLE(:T). Производительность не является проблемой в этом вопросе. - person andreaplanet; 13.03.2014
comment
Да, в этой версии по-прежнему используются два шага, но первый шаг теперь проще и не требует указания всех столбцов. Я не думаю, что есть способ сделать это за один шаг. - person Jon Heller; 13.03.2014