CODEWITHSUNDEEP
sqloracle-databaseplsql
Logesh Varathan
Logesh Varathan

Reputation: 33

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

I tried to select values from the nested table and bulk collecting into an associative array collection. When I try to bulk collect oracle throwing the above exception(PL/SQL: SQL Statement ignored PL/SQL: ORA-22905: cannot access rows from a non-nested table item) though I fetch the data from the nested table.

It is not happening in all the cases. When the same package compiled in the different client database, Some case it is not throwing an error and in some environment, it is throwing an error. Can you please help what was the exact issue.

I have not attached the entire package. Instead provided the case where the issue occurs.

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
  SELECT DISTINCT rc_id,
         doc_num BULK COLLECT
  INTO   rc_tab_type_dist_rc
  FROM   TABLE(rc_tab_type);
END;

Upvotes: 1

Views: 5240

Answers (1)

MT0
MT0

Reputation: 167981

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;

Upvotes: 1

Related Questions