Oracle Execute Immediate with DDL and Nested table

Question

I have a problem trying to use an Execute Immediate statement containing a CREATE TABLE statement and a user defined Table Type. I get error ORA-22905 on Oracle 11g.
Is there any workaround to solve this issue?

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;

The real code for the SELECT * FROM TABLE(:T) is dynamic (main table name is temporary) and slow. That's why I try to avoid creating the table in two steps (as done with MY_TABLE1). Also with two steps I can't use SELECT * but I have to specify all the columns (variable amount and over 100 columns).

Is there a reason you couldn't use a view on the original table instead of creating and populating a temp table? — Bob Jarvis - Reinstate Monica, Mar 12 '14 at 22:25
This code is executed in a job and can take up to 2 hours to complete and will extract up to 500.000 records. The result can be viewed immediately and multiple times (user session lifetime of those "temporary" tables). — andreaplanet, Mar 12 '14 at 22:45
So it takes two hours to build this table by moving the data all over the place. How long does it take just to execute the query without all the storage allocation and etc? — Bob Jarvis - Reinstate Monica, Mar 12 '14 at 23:10
It can take up two hours to do the SELECT because the records are extracted from a table of over 300 milion records. The average execution time is between 2 and 10 minutes, sometimes with large search values it takes up to 2 hours. Creating the table and inserting the data is a relatively fast process (a question of seconds). — andreaplanet, Mar 12 '14 at 23:16
Also using a view I could not bind the variables, this creates an error: EXECUTE IMMEDIATE 'CREATE VIEW MY_TABLE2 AS SELECT * FROM TABLE(:T)' USING MT; — andreaplanet, Mar 12 '14 at 23:41
Related: [Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?](http://stackoverflow.com/q/25489002/1461424) — sampathsris, Aug 26 '14 at 04:08

score 0 · Answer 1 · answered Mar 13 '14 at 06:07

There is likely a way to completely avoid this issue. Skip the bulk collect and use a simple CREATE TABLE MY_TABLE AS SELECT * FROM DUAL; That may be an over-simplification of the real logic to gather the data. But there is almost always a way to bypass a bulk collect and store the data directly in an object with just SQL.

If a PL/SQL solution is truly needed, the error ORA-22905: cannot access rows from a non-nested table item can be avoided by creating an object type and creating the table based on that type. This may not solve the performance issue, but at least this avoids the need to re-specify all the columns in the table 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;
/

You are still using two steps (as in my first example that works). In the real code the MY_TABLE1/2 has a variable number of over 100 columns and the SELECT is a join between a big table and the TABLE(:T). Performance is not an issue in this question. — andreaplanet, Mar 13 '14 at 11:23
Yes this version still uses two steps, but the first step is now simpler and does not require specifying all the columns. I don't think there is a way to do this in one step. — Jon Heller, Mar 13 '14 at 13:16

Oracle Execute Immediate with DDL and Nested table

1 Answers1