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

Question

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;

MT0 · Accepted Answer · 2018-03-27 12:46:11Z

1

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;

answered Mar 27, 2018 at 12:46

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Logesh Varathan Over a year ago

Hi, I agree with you on the version part. The two environment where am compiling is on 11g and 12c where the code is successfully compiling in 12c but not on 11g. Might be that causes the issue. The select query is written on top of the nested table collection, not the associative array. Thanks a lot.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related