CODEWITHSUNDEEP
oracle-databaseplsql
Arun Kumar
Arun Kumar

Reputation: 57

PL/SQL error reference to uninitialised collection error even when its initialised

I have a PL/SQL script which used nested table. Below is the sample code.

type rec is record 
(
--col data types here
)

type rec_table is table of rec;
v_rec_table rec_table := rec_table(); --initialising here.
arr_size integer := 0;   --edit 1
...
...
begin
...
...
open cursor;
loop
    fetch cursor bulk collect into v_rec_table limit arr_size; --edit

if nvl(v_rec_table.last,0) > 0 --it shows error is here.
then
...
...
end if;

The error is ORA-06531: Reference to uninitialized collection. Please help me debug this one.

Upvotes: 0

Views: 277

Answers (3)

Arun Kumar
Arun Kumar

Reputation: 57

Apologies since i did not post whole code. The error occurred because i did not add index by to two columns in the record definition.

After reading through few articles i found my flaw.

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Although your problem is not reproducible, there are few things I found could be improved in your code.

If you are using a bulk collect, the collection is initialised automatically and hence, this line is not required

v_rec_table rec_table := rec_table();

Also, as @Littlefoot mentioned, LOOP is not required unless you are using the LIMIT clause.

You may use the COUNT collection function instead of nvl(v_rec_table.last,0)

v_rec_table.count

Instead of defining a record type and bulk collecting into it, you may define a collection of cursor%ROWTYPE;

CURSOR cur is SELECT column1,column2 FROM tablename;
type    rec_table is table of cur%ROWTYPE;
v_rec_table rec_table;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

If would help if you posted complete (sample) code (instead of "...").

Here's an example based on Scott's schema which works OK (your error line is line 14):

SQL> declare
  2    type rec is record (id number);
  3    type rec_table is table of rec;
  4    v_rec_table rec_table := rec_table();
  5
  6    cursor c1 is select empno from emp;
  7  begin
  8    open c1;
  9    loop
 10      exit when c1%notfound;
 11      fetch c1 bulk collect into v_rec_table;
 12    end loop;
 13    dbms_output.put_line('last = ' || v_rec_table.last);
 14    if nvl(v_rec_table.last, 0) > 0 then
 15       dbms_output.put_line('last exists');
 16    end if;
 17    close c1;
 18  end;
 19  /
last = 12
last exists

PL/SQL procedure successfully completed.

Though, I'm not sure what's that LOOP used for, as you could have done it as

SQL> declare
  2    type rec is record (id number);
  3    type rec_table is table of rec;
  4    v_rec_table rec_table := rec_table();
  5  begin
  6    select empno bulk collect into v_rec_table from emp;
  7    dbms_output.put_line('last = ' || v_rec_table.last);
  8  end;
  9  /
last = 12

PL/SQL procedure successfully completed.

Upvotes: 2

Related Questions