我有一个问题,试图使用包含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;
/