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) ;

当它到达 SELECT from TABLE 语句时，我收到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 之外.

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 成为嵌套表类型.(可变数组也可以在这里工作，但我不建议使用它们，除非您知道预期行数的上限.声明可变数组类型时，您需要指定最大元素数，而嵌套表类型有没有这样的限制.)

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 中声明的.您需要在 PL/SQL 之外声明 type_tab_AB 和 record_AB，使用 CREATE TYPE ....

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 游标变量列出其内容.当我在 Oracle 11g XE 上运行脚本时，在所有类型创建"和PL/SQL 过程成功完成"消息之后，我得到的输出如下:

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屋！