sql - ORA-22905 - when querying a table type with a select statement

Question

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

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

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

Answer 1

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.

The error

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

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: local collection types not allowed in SQL statements

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 ....

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.

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: not enough values

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 ....

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

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

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.