从集合类型 oracle 12c 插入表 - ORA-00902: 无效数据类型 [英] Insert into table from collection type oracle 12c - ORA-00902: invalid datatype

问题描述

我正在使用 Oracle 12.1 我以为我可以查询 12c 中的表类型.当我尝试执行此包时，我收到错误 ORA-00902:无效数据类型.我什至尝试使用 cast multiset，但仍然出现相同的错误.

I am using Oracle 12.1 I thought I can query the table types in 12c.I get error ORA-00902: invalid datatype when I try to execute this package. I even tried using cast multiset,but still same error.

我知道我们可以在数据库级别创建对象然后查询，但我不想这样做.

I know we can create object at database level and then query, but I don't want to.

CREATE OR REPLACE PACKAGE test123 AS
 TYPE typ1 IS RECORD(col1 VARCHAR2(100),col2 VARCHAR2(100));
 TYPE tab_typ IS TABLE OF typ1 INDEX BY BINARY_INTEGER;
v_tab tab_typ;

PROCEDURE p1;
END;
/
CREATE OR REPLACE PACKAGE BODY test123 AS

PROCEDURE p1 IS
 BEGIN
  SELECT c1,c2 BULK COLLECT INTO v_tab FROM tabx;            
   INSERT INTO taby
     SELECT * FROM TABLE(v_tab);
  END;
END;
/

EXEC test123.p1;
--ORA-00902: invalid datatype

推荐答案

select * from table 在 Cursor 或 for 循环中工作正常，但不当我将它用于 INSERT Oracle Database 12c 企业版时版本 12.1.0.2.0 - 64 位生产 PL/SQL 版本 12.1.0.2.0 -适用于 Linux 的生产 CORE 12.1.0.2.0 生产 TNS:版本12.1.0.2.0 - 生产 NLSRTL 版本 12.1.0.2.0 - 生产

The select * from table works fine in Cursor or for Loop, but does not when I use it for INSERT Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production

在仔细查看您的查询后，我发现您是对的.插入 不起作用.它看起来也是正确的.我们已经有了 FORALL INSERT 来将数据从集合插入到表中.因此排除了额外的INSERT as Select Statement 的必要性.但是，您可以使用 SELECT 语句在查询的 Where 子句中使用集合.要制作和插入，您只需按照以下步骤操作即可.

After carefully revisiting your query, I found that you were correct. Insert doesnot work. And it looks correct as well. We already have FORALL INSERT to get data inserted from the collection to a table. Hence the necessasity of having a additional INSERT as Select Statement is ruledout. However you can use the SELECT statement using a collection in Where clause of the query. To make and insert you can simply follow the below steps.

CREATE OR REPLACE PACKAGE BODY test123 
AS
PROCEDURE p1 IS
 BEGIN
  SELECT c1,c2 BULK COLLECT INTO v_tab FROM tabx;  

  ForAll rec in 1..v_tab.count
   INSERT INTO taby
    values v_tab(rec);
     --SELECT * FROM TABLE(v_tab);
  END;
END;
/

如果你想在Select语句中使用PLSQL作用域下声明的Type，你可以使用如下:

Incase you want to use the Type declared under PLSQL scope in Select statement, you can use as below:

DECLARE
 TYPE typ1 IS RECORD(col1 VARCHAR2(100),col2 VARCHAR2(100));
 TYPE tab_typ IS TABLE OF typ1 INDEX BY BINARY_INTEGER;
 v_tab tab_typ;
BEGIN

 SELECT col1,col2 BULK COLLECT INTO v_tab FROM tabx;  

  DELETE FROM taby
    WHERE (col1,col2) in (Select * from table(v_tab)); 

END;
/

这篇关于从集合类型 oracle 12c 插入表 - ORA-00902: 无效数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！