Ora-22905 - When Querying a Table Type with a Select Statement

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

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 ('Ora-22905 - When Querying a Table Type with a Select Statementaa 2', 'b 2');
INSERT INTO some_table (AA, BB) VALUES ('aaaaa 3', 'bbbbbb 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
Ora-22905 - When Querying a Table Type with a Select Statementaa 2     b 2
aaaaa 3          bbbbbb 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.

PL/SQL Statement Ignored. ORA-22905: cannot access rows from a non-nested table item

You cannot do this using SQL; an associative array is a PL/SQL data type and cannot be used in the SQL scope. In a similar vein, collections defined in the PL/SQL scope cannot be used in SQL scope (in 11g and earlier) - you either need to define collections in the SQL scope (Note - you cannot do this for associative arrays as they are purely PL/SQL) or just use PL/SQL.

Assuming the rc_tab_type collection is not sparse then you can use pure PL/SQL like this:

DECLARE
  TYPE rc_rec_multiset IS record (
      rc_id   NUMBER,
      doc_num VARCHAR2(100)
    );

  TYPE rc_type IS TABLE OF rc_rec_multiset;

  TYPE tab_rec_type_multiset IS
    TABLE OF rc_rec_multiset INDEX BY pls_integer;

  rc_tab_type         rc_type               := rc_type();
  rc_tab_type_dist_rc tab_rec_type_multiset;
BEGIN
  /*
   * Populate rc_tab_type here.
   */

  FOR i IN 1 .. rc_tab_type.COUNT LOOP
    rc_tab_type_dist_rc( rc_tab_type(i).rc_id ) := rc_tab_type(i).doc_num;
  END LOOP;
END;

If it is sparse then, instead of the FOR loop, you will have to use:

i := rc_tab_type.FIRST;
WHILE i IS NOT NULL LOOP
  rc_tab_type_dist_rc( rc_tab_type(i).rc_id ) := rc_tab_type(i).doc_num;
  i := rc_tab_type.NEXT(i);
END LOOP;

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

You have to declare the type on "the database level" as ammoQ suggested:

CREATE TYPE subjectid_tab AS TABLE OF NUMBER INDEX BY binary_integer;

instead of declaring the type within PL/SQL. If you declare the type just in the PL/SQL block, it won't be available to the SQL "engine".

ORA-22905 - While using DBMS_SQL.Number_Table for Table Operator

Although it is not clear what you are trying to accomplish, there are two things which are wrong with your pl/sql block.

• You can use the TABLE() function on the variables of collection
  types defined in the schema and not on those defined locally

• The way you are running your DML ( delete ) statement is wrong, it throws the error `

ORA-22841: DML on PL/SQL collections not supported.

So, create a collection TYPE in your schema and declare a collection of that type in your PL/SQL block and for your delete use an IN condition

CREATE OR REPLACE TYPE idlisttable as TABLE OF NUMBER;

DECLARE l_tidlist idlisttable;

BEGIN
    SELECT ct.id BULK collect
    INTO l_tidlist
    FROM collect_test ct
    WHERE MOD(ct.id, 2) = 0;

    DELETE
    FROM collect_test ct
    WHERE ct.id IN (
            SELECT ids.column_value
            FROM TABLE (l_tidlist) ids
            );

    ROLLBACK;
END;
/

`

Using table type in SELECT statement

If you don't want to declare your own type at schema level, you could use a built-in table type, as long as there is one that matches your underlying data type and you don't have too many values to put in it:

DECLARE
  TYPE test_type IS TABLE OF user_objects.object_id%TYPE
    INDEX BY BINARY_INTEGER;
  test_table1 test_type;
  test_table2 test_type;
  databaseArray sys.odcinumberlist;
BEGIN
  SELECT object_id
  BULK COLLECT INTO test_table1
  FROM user_objects;

  databaseArray := sys.odcinumberlist();
  FOR i IN 1..test_table1.COUNT LOOP
    databaseArray.extend;
    databaseArray(i) := test_table1(i);
  END LOOP;

  SELECT column_value
  BULK COLLECT INTO test_table2
  FROM TABLE(databaseArray);
END;
/

anonymous block completed

---

Related Topics