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
Също така използвайки изглед, не можах да обвържа променливите, това създава грешка: EXECUTE IMMEDIATE 'CREATE VIEW MY_TABLE2 AS SELECT * FROM TABLE(:T)' USING 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