Oracle 使用 DDL 和嵌套表立即执行

Question

我在尝试使用包含 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 列)。

Answer 1

可能有一种方法可以完全避免这个问题。跳过批量收集并使用简单的 CREATE TABLE MY_TABLE AS SELECT * FROM DUAL; 这可能是对收集数据的实际逻辑的过度简化。但是几乎总有一种方法可以绕过批量收集并仅使用 SQL 将数据直接存储在对象中。

如果确实需要 PL/SQL 解决方案，错误 ORA-22905:无法访问非嵌套表项中的行 可以通过创建对象类型并基于那种。这可能无法解决性能问题，但至少可以避免重新指定表 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;
/