ORA-22905-使用select语句查询表类型时 [英] ORA-22905 - when querying a table type with a select statement

问题描述

 DECLARE
 TYPE record_AB IS RECORD
   (
      AA              VARCHAR2 (16 BYTE),
      BB    VARCHAR2 (16 BYTE)
   );

  TYPE type_tab_AB IS TABLE OF record_AB
                        INDEX BY BINARY_INTEGER;

  tab_AB   type_tab_AB;

  BEGIN
   SELECT *
    BULK COLLECT INTO tab_AB FROM...
    ..
    SELECT * FROM TABLE (tab_AB) ;

当它从TABLE语句进入SELECT时，出现"ORA-22905:无法从非嵌套表项目访问行"的情况.

I get "ORA-22905: cannot access rows from a non-nested table item" when it gets to the SELECT from TABLE statement.

甚至可以在PLSQL中查询表类型吗?

Is it even possibile to query a table type within PLSQL?

推荐答案

可以在PL/SQL中查询表类型，但只能查询其类型在架构级别(即，PL/SQL外部)声明的嵌套表和varray.

It is possible to query table types in PL/SQL, but only nested tables and varrays whose types are declared at schema-level, i.e. outside of PL/SQL.

错误

ORA-22905:无法访问非嵌套表项中的行

ORA-22905: cannot access rows from a non-nested table item

表示您正尝试从不受支持的表类型中查询.由于INDEX BY BINARY_INTEGER子句，您的类型type_tab_AB是关联数组.删除INDEX BY BINARY_INTEGER子句，使您的type_tab_AB成为嵌套表类型. (Varrays也可以在这里工作，但是除非您知道预期的行数上限，否则我不建议您使用它们.在声明varray类型时，您需要指定最大元素数，而嵌套表类型具有没有这样的限制.)

means that you are trying to query from an unsupported table type. Your type type_tab_AB is an associative array, because of the INDEX BY BINARY_INTEGER clause. Remove the INDEX BY BINARY_INTEGER clause to make your type_tab_AB a nested table type. (Varrays would also work here, but I wouldn't recommend using them unless you know an upper bound for the number of rows to expect. When declaring a varray type, you need to specify the maximum number of elements, whereas nested table types have no such restriction.)

进行此更改后，您的代码可能仍无法工作.您可能会遇到的下一个错误(如果没有，请参阅底部的注释)

After making this change, your code may still not work. The next error you may get (see note at bottom if you don't) is

PLS-00642:SQL语句中不允许使用本地集合类型

PLS-00642: local collection types not allowed in SQL statements

这是因为要选择的类型是在PL/SQL中声明的.您需要使用CREATE TYPE ...在PL/SQL之外声明type_tab_AB和record_AB.

This is because the type you are selecting into is declared inside PL/SQL. You need to declare type_tab_AB, and record_AB outside of PL/SQL, using CREATE TYPE ....

您遇到的下一个问题将是由于关键字RECORD.记录类型只能在PL/SQL内部创建，它们不能在架构级别创建.将RECORD更改为OBJECT即可解决此问题.

The next problem you encounter will be because of the keyword RECORD. Record types can only be created inside PL/SQL, they cannot be created at schema level. Change RECORD to OBJECT to fix this.

您将遇到的最后一个问题是SELECT t.AA, t.BB BULK COLLECT INTO tab_AB FROM ...语句.就目前而言，此查询将为您提供以下错误:

The last problem you will encounter is with the SELECT t.AA, t.BB BULK COLLECT INTO tab_AB FROM ... statement. As it stands, this query will give you the following error:

PL/SQL:ORA-00947:值不足

PL/SQL: ORA-00947: not enough values

您正在从每一行中选择两项，并且仅提供一个表以将数据批量插入其中. Oracle不太了解您是否要将这两项填充到您的record_AB类型中.您可以通过将查询更改为SELECT record_AB(t.AA, t.BB) BULK COLLECT INTO tab_AB FROM ...来轻松解决此问题.

You are selecting two items from each row and are providing only one table to bulk-insert the data into. Oracle can't quite figure out that you want to stuff the two items into your record_AB type. You can fix this fairly easily by changing the query to SELECT record_AB(t.AA, t.BB) BULK COLLECT INTO tab_AB FROM ....

总体而言，这些更改可以解决该问题.这是完整的SQL * Plus脚本，该脚本创建带有一些测试数据的测试表并验证其可以查询表类型:

Collectively these changes should fix the problem. Here's a full SQL*Plus script that creates a test table with some test data and verifies that it can query the table type:

CREATE TABLE some_table (AA VARCHAR2(16 BYTE), BB VARCHAR2(16 BYTE));

INSERT INTO some_table (AA, BB) VALUES ('aa 1', 'bb 1');
INSERT INTO some_table (AA, BB) VALUES ('aaaaaaaaaa 2', 'b 2');
INSERT INTO some_table (AA, BB) VALUES ('aaaaa 3', 'bbbbbbbbbbbbbb 3');
COMMIT;

VARIABLE curs REFCURSOR;

CREATE OR REPLACE TYPE record_AB AS OBJECT
   (
      AA    VARCHAR2 (16 BYTE),
      BB    VARCHAR2 (16 BYTE)
   );
/

CREATE OR REPLACE TYPE type_tab_AB IS TABLE OF record_AB;
/

DECLARE
  tab_AB   type_tab_AB;
BEGIN
  SELECT record_AB(t.AA, t.BB)
    BULK COLLECT INTO tab_AB 
    FROM some_table t;

  OPEN :curs FOR SELECT * FROM TABLE (tab_AB) ;
END;
/

PRINT :curs

我将SELECT将tab_AB的内容放入光标中，并使用SQL * Plus光标变量列出了其内容.在所有创建类型"和成功完成PL/SQL过程"消息之后，在Oracle 11g XE上运行脚本时得到的输出如下:

I've put the result of SELECTing the contents of tab_AB into a cursor, and used a SQL*Plus cursor variable to list its contents. The output I get when I run the script on Oracle 11g XE, after all of the 'Type created' and 'PL/SQL procedure successfully completed' messages, is as follows:

AA               BB
---------------- ----------------
aa 1             bb 1
aaaaaaaaaa 2     b 2
aaaaa 3          bbbbbbbbbbbbbb 3

注意:为简单起见，我假设发问者使用的是Oracle 11或更早版本.在Oracle 12中，我相信您可以在SQL查询中使用PL/SQL中声明的类型，因此您可能不会遇到PLS-00642错误.我不能说我的答案还需要对Oracle 12进行哪些其他更改，因为我还没有使用Oracle 12.

NOTE: For simplicity, I've assumed the questioner is using Oracle 11 or older. In Oracle 12, I believe you are allowed to use types declared in PL/SQL in a SQL query, so you may not encounter the PLS-00642 error. I can't say what other changes to my answer might also be necessary for Oracle 12 as I have yet to use Oracle 12.

这篇关于ORA-22905-使用select语句查询表类型时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！