Thanks for the question, Mathews.

Asked: February 23, 2016 - 3:47 am UTC

Last updated: July 16, 2020 - 1:06 am UTC

Version: 11g 0 down vote favorite I am getting "ORA-06531: Reference to uninitialized collection" even after initializing collection to assign a value. Could you please help. Please find the code below.

Viewed 100K+ times! This question is

You Asked

Hi Tom,

I am getting "ORA-06531: Reference to uninitialized collection" even after initializing collection to assign a value. Could you please help. Please find the code below.

create or replace TYPE TYP_GRP_ID is object (grp_id number);
create or replace TYPE TAB_GROUP is table of TYP_GRP_ID;

create or replace PACKAGE TEST AS
PROCEDURE TEST;
END;

create or replace PACKAGE BODY TEST AS
PROCEDURE TEST
IS
acc_arr TAB_GROUP;
BEGIN
acc_arr.EXTEND;
acc_arr := new TAB_GROUP(null);
acc_arr(acc_arr.LAST).grp_id := 1000;
dbms_output.put_line(acc_arr(acc_arr.LAST).grp_id);
END;
END;

Thanks in advance
Mathew

and Connor said...

An array of objects isnt really a "n-dimensonal" array, its a 1-d array of complex types. So the syntax needs to reflect that, eg

SQL> create or replace PACKAGE BODY TEST AS
  2
  3  PROCEDURE TEST
  4  IS
  5  acc_arr TAB_GROUP := TAB_GROUP();
  6  BEGIN
  7  acc_arr := TAB_GROUP(null);
  8  acc_arr(acc_arr.LAST) := TYP_GRP_ID(1000);
  9  dbms_output.put_line(acc_arr(acc_arr.LAST).grp_id);
 10  END;
 11  END;
 12  /

Package body created.

SQL>
SQL> set serverout on
SQL> exec test.test
1000

PL/SQL procedure successfully completed.

You can see line 8, I'm assigning an *object* to the array, not the components of the object.

Hope this helps.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 23, 2016 - 6:17 am UTC

Thank you very much.

Bulk collect

Rajasekhar, July 09, 2020 - 11:13 am UTC

Hello Chris,

After fetch and bulk collect into plsql collection variable using limit, i can exit loop the with condition (collection variable.count)=0.but i can't able to see the data in collection variable after the exit
can you explain me how to hold the data in collection variable even after existing the loop.
create or replace package demo1_pkg
as
type test_process is table of t_po_invoice%rowtype;
procedure do_insert;
end;
/

create or replace package body demo1_pkg
as
procedure do_insert
is
test_process_db1 test_process:=test_process();
cursor CUR_1 is select * from test_table;
begin
OPEN CUR_1;
LOOP
FETCH CUR_1
BULK COLLECT INTO test_process_db1;
LIMIT 10000;
dbms_output.put_line('fetch completed'||CUR_1%rowcount);
dbms_output.put_line('bulk collect completed'||test_process_db1.count);
EXIT WHEN test_process_db1.count = 0;
END LOOP;
dbms_output.put_line('the count'||test_process_db1.count));
close CUR_1;
exception
when others then
raise_application_error(-20000,'the error'||SQLERRM);
end;
end;
/
fetch completed100000
bulk collect completed100000
fetch completed200000
bulk collect completed100000
fetch completed300000
bulk collect completed100000
fetch completed400000
bulk collect completed100000
fetch completed500000
bulk collect completed100000
fetch completed600000
bulk collect completed100000
fetch completed700000
bulk collect completed100000
fetch completed800000
bulk collect completed100000
fetch completed860600
bulk collect completed60600
fetch completed860600
bulk collect completed0
the value0

July 09, 2020 - 11:24 am UTC

It was Connor who answered originally, but hey ;)

When you exit there is no data in the collection. That's why the loop exits - there were no more rows left to fetch from the cursor.

The point of bulk collecting with a limit is to stop you running out of memory.

If you want to keep all the rows you've fetched so far, you need to copy them into another array. Or you could bulk collect without the limit:

select * 
bulk collect into ...
from test_table;

But this risks you running out of memory and the process failing!

Thanks

Rajasekhar, July 09, 2020 - 1:41 pm UTC

Hello Chris,

Thank you so much for the info.

can i assign the plsql variable directly to other plsql variable or do i need to follow any incremental assignment when we use limit?
declare
var 1;
var2;
begin
open c_1;
loop
fetch c_1 into v1 limit 1000;
var2 :=var1 ; or var2:=var2+var1;
exit when var1.count=0;
end loop;
close c_1;
end;
/

Thanks in advance

July 09, 2020 - 3:31 pm UTC

You can assign one array to another. But this will overwrite the contents of the target!

This is fine if you want to access the values from the previous/last fetch outside the loop. But if you want to fill an array with all the rows you fetch, this won't work.

If you do want an array with all the rows from the query, as I suggested before ditch the loop and have a single statement:

select * 
bulk collect into ...
from test_table;

Bulk collect over dblink

Rajasekhar, July 15, 2020 - 5:00 pm UTC

Hello Chris

This is fine if you want to access the values from the previous/last fetch outside the loop -----can you provide me an example for using previous fetch values outside the loop.

As you said earlier, below statement will overload memory. if table has huge data. Still it is better idea in order to load collection in one go?if yes, can we perform the same over dblink?
select * bulk collect into collection;

Thanks in advance.

July 16, 2020 - 1:06 am UTC

If you have a concern about volume, you can always fetch data from a remote source and store in a local global temporary table. This will spool to disk if it gets too large for PGA

Breadcrumb

Question and Answer