Oracle立即执行DDL和嵌套表



我有一个问题,试图使用包含CREATE TABLE语句和用户定义的表类型的Execute Immediate语句。我在Oracle 11g上得到错误ORA-22905
有解决这个问题的方法吗?

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列)。

可能有一种方法可以完全避免这个问题。跳过批量收集并使用简单的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;
/

相关内容

  • 没有找到相关文章

最新更新