Skip to Main Content
  • Questions

  • SQLERRM:ORA-06531: Reference to uninitialized collection

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Partha pratim.

Asked: June 18, 2018 - 7:33 am UTC

Last updated: June 19, 2018 - 3:21 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked 

Hi I am facing this error. my script is like below. please suggest:
/
create table address_test(
Addr_id number,
addr_cus_id number,
street_name varchar2(100),
town varchar2(100),
county varchar2(100),
sub_county_state_province varchar2(100),
country varchar2(100),
post_zip_code number,
lattude varchar2(100),
longitude varchar2(100),
accuracy_level number
);
/
CREATE OR REPLACE TYPE SITE_ADDR_OBJECT AS OBJECT
(
    site_name                varchar2(50),
    site_cus_id              number,
 street_name varchar2(100),
 town varchar2(100),
 county varchar2(100),
 sub_county_state_province varchar2(100),
 country varchar2(100),
 post_zip_code number,
 lattude varchar2(100),
 longitude varchar2(100),
 accuracy_level number
 
);
/
CREATE OR REPLACE TYPE SITE_ADDR_OBJECT_ARRAY AS TABLE OF SITE_ADDR_OBJECT;
/
CREATE OR REPLACE TYPE SITE_ADDR_OBJECT_OP AS OBJECT
(
    site_id                number,
    site_name              varchar2(50)
 
);
/
CREATE OR REPLACE TYPE SITE_ADDR_OBJECT_ARRAY_OP AS TABLE OF SITE_ADDR_OBJECT_OP;
/
create sequence site_no start with 1 increment by 1;
/
create sequence addr_id start with 1 increment by 1;
/
create table SITE_TEST(site_id NUMBER PRIMARY KEY,site_name VARCHAR2(30),site_cus_id NUMBER,site_addr_id number);
/
create or replace
procedure SITE_ADDR_INSERT_2( test_site_addr in SITE_ADDR_OBJECT_ARRAY,
 test_site_addr_op out SITE_ADDR_OBJECT_ARRAY_OP)
  as 
  V_site_no NUMBER;
  v_addr_id number;
  vv_addr_id number;
  v_site_name varchar2(50);
  begin 
  for i in 1 .. test_site_addr.count 
  LOOP 
  V_site_no:=site_no.NEXTVAL;
  v_addr_id:=addr_id.NEXTVAL;
  
  insert into address_test(Addr_id,addr_cus_id,street_name,town,county,sub_county_state_province,country,post_zip_code,lattude,longitude,accuracy_level)
  values (v_addr_id,test_site_addr(i).site_cus_id,test_site_addr(i).street_name,test_site_addr(i).town,test_site_addr(i).county,test_site_addr(i).sub_county_state_province,test_site_addr(i).country,
  test_site_addr(i).post_zip_code,test_site_addr(i).lattude,test_site_addr(i).longitude,test_site_addr(i).accuracy_level);
  
  select Addr_id into vv_addr_id from address_test  where  post_zip_code=test_site_addr(i).post_zip_code and addr_cus_id=test_site_addr(i).site_cus_id;
  
  DBMS_OUTPUT.PUT_LINE('vv_addr_id:'||vv_addr_id);
  
  INSERT INTO SITE_TEST(site_id,site_name,site_cus_id,site_addr_id) VALUES(V_site_no,test_site_addr(i).site_name,test_site_addr(i).site_cus_id,vv_addr_id);
  v_site_name:=test_site_addr(i).site_name;
  DBMS_OUTPUT.PUT_LINE('successful');
  test_site_addr_op(i).site_id:=V_site_no;
  test_site_addr_op(i).site_name:=v_site_name;
  DBMS_OUTPUT.PUT_LINE('site_id: '||test_site_addr_op(i).site_id);
  DBMS_OUTPUT.PUT_LINE('site_name: '||test_site_addr_op(i).site_name);
  end loop;
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('SQLERRM:'||SQLERRM);
  DBMS_OUTPUT.PUT_LINE('SQLCODE:'||sqlcode);
end;
/
--while calling the procedure the below script I am using:
/
SET SERVEROUTPUT ON;
DECLARE
  TEST_SITE_ADDR BFG_OWNER.SITE_ADDR_OBJECT_ARRAY;
  TEST_SITE_ADDR_OP BFG_OWNER.SITE_ADDR_OBJECT_ARRAY_OP;
BEGIN
  -- Modify the code to initialize the variable
   TEST_SITE_ADDR := SITE_ADDR_OBJECT_ARRAY(SITE_ADDR_OBJECT('ChI08',1121,'Ser_Rd','Waterloo','Black_Hawk_County','Iowa','US',507011,'42.4569376','-92.4082',0),SITE_ADDR_OBJECT('ChIbm0',1131,'Alton_Court','Eldersburg','Carroll_County','Maryland','US',217841,'39.4118','-76.933',2),SITE_ADDR_OBJECT('EjEJv',1141,'Pow_Lakes_Rd','Powhatan','Powha_County','Virginia','US',231391,'37.57711','-77.9927',1),SITE_ADDR_OBJECT('EiWNo',1151,'Essex_Road','Greenwich','Fairf_County','Connecticut','US',068311,'41.028098','-73.666',1),SITE_ADDR_OBJECT('ChI1E',1161,'Kild_Farm_Road','Cary','Wake_County','North_Carolina','US',275111,'35.7455127','-78.7803',0),SITE_ADDR_OBJECT('ChII8',1171,'Waky_Hollow_Court','Wappi_Falls','Dutchess_County','NY','US',125901,'41.5964825','-73.910970',3),SITE_ADDR_OBJECT('EiJs',1181,'Sleepy_Court','Ambler','Montg_County','Pennsylvania','US',190021,'40.2227951','-75.1971',2),SITE_ADDR_OBJECT('ChIIU',1191,'Sleepy_Court','Perth_Amboy','Middlesex_County','New_Jersey','US',088611,'40.50909','-74.2766',3),SITE_ADDR_OBJECT('ChIzs',1201,'West_Street','Park_Ridge','Cook_County','Illinois','US',600681,'42.017220','-87.86121',0),SITE_ADDR_OBJECT('ChI38',1211,'Sleepy_Court','Monroe','Orange_County','New_York','US',109501,'41.3078975','-74.20619',3));
  SITE_ADDR_INSERT_2(
    TEST_SITE_ADDR => TEST_SITE_ADDR,
    TEST_SITE_ADDR_OP => TEST_SITE_ADDR_OP
  );
  -- Modify the code to output the variable
  -- DBMS_OUTPUT.PUT_LINE('TEST_SITE_ADDR_OP = ' || TEST_SITE_ADDR_OP);
END;
/
--I am getting below error:
anonymous block completed
vv_addr_id:202
successful
SQLERRM:ORA-06531: Reference to uninitialized collection
SQLCODE:-6531

and Connor said...

The first thing I do is take out the 'when others' - that way we can see exactly where the error occurred

SQL> create table address_test(
  2  Addr_id number,
  3  addr_cus_id number,
  4  street_name varchar2(100),
  5  town varchar2(100),
  6  county varchar2(100),
  7  sub_county_state_province varchar2(100),
  8  country varchar2(100),
  9  post_zip_code number,
 10  lattude varchar2(100),
 11  longitude varchar2(100),
 12  accuracy_level number
 13  );

Table created.

SQL> CREATE OR REPLACE TYPE SITE_ADDR_OBJECT AS OBJECT
  2  (
  3      site_name                varchar2(50),
  4      site_cus_id              number,
  5   street_name varchar2(100),
  6   town varchar2(100),
  7   county varchar2(100),
  8   sub_county_state_province varchar2(100),
  9   country varchar2(100),
 10   post_zip_code number,
 11   lattude varchar2(100),
 12   longitude varchar2(100),
 13   accuracy_level number
 14
 15  );
 16  /

Type created.

SQL> CREATE OR REPLACE TYPE SITE_ADDR_OBJECT_ARRAY AS TABLE OF SITE_ADDR_OBJECT;
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE SITE_ADDR_OBJECT_OP AS OBJECT
  2  (
  3      site_id                number,
  4      site_name              varchar2(50)
  5
  6  );
  7  /

Type created.

SQL> CREATE OR REPLACE TYPE SITE_ADDR_OBJECT_ARRAY_OP AS TABLE OF SITE_ADDR_OBJECT_OP;
  2  /

Type created.

SQL> create sequence site_no start with 1 increment by 1;

Sequence created.

SQL>
SQL> create sequence addr_id start with 1 increment by 1;

Sequence created.

SQL>
SQL> create table SITE_TEST(site_id NUMBER PRIMARY KEY,site_name VARCHAR2(30),site_cus_id NUMBER,site_addr_id number);

Table created.

SQL>
SQL> create or replace
  2  procedure SITE_ADDR_INSERT_2( test_site_addr in SITE_ADDR_OBJECT_ARRAY,
  3   test_site_addr_op out SITE_ADDR_OBJECT_ARRAY_OP)
  4    as
  5    V_site_no NUMBER;
  6    v_addr_id number;
  7    vv_addr_id number;
  8    v_site_name varchar2(50);
  9    begin
 10    for i in 1 .. test_site_addr.count
 11    LOOP
 12    V_site_no:=site_no.NEXTVAL;
 13    v_addr_id:=addr_id.NEXTVAL;
 14
 15    insert into address_test(Addr_id,addr_cus_id,street_name,town,county,sub_county_state_province,country,post_zip_code,lattude,longitude,accuracy_level)
 16    values (v_addr_id,test_site_addr(i).site_cus_id,test_site_addr(i).street_name,test_site_addr(i).town,test_site_addr(i).county,test_site_addr(i).sub_count
y_state_province,test_site_addr(i).country,
 17    test_site_addr(i).post_zip_code,test_site_addr(i).lattude,test_site_addr(i).longitude,test_site_addr(i).accuracy_level);
 18
 19    select Addr_id into vv_addr_id from address_test  where  post_zip_code=test_site_addr(i).post_zip_code and addr_cus_id=test_site_addr(i).site_cus_id;
 20
 21    DBMS_OUTPUT.PUT_LINE('vv_addr_id:'||vv_addr_id);
 22
 23    INSERT INTO SITE_TEST(site_id,site_name,site_cus_id,site_addr_id) VALUES(V_site_no,test_site_addr(i).site_name,test_site_addr(i).site_cus_id,vv_addr_id);

 24    v_site_name:=test_site_addr(i).site_name;
 25    DBMS_OUTPUT.PUT_LINE('successful');
 26    test_site_addr_op(i).site_id:=V_site_no;
 27    test_site_addr_op(i).site_name:=v_site_name;
 28    DBMS_OUTPUT.PUT_LINE('site_id: '||test_site_addr_op(i).site_id);
 29    DBMS_OUTPUT.PUT_LINE('site_name: '||test_site_addr_op(i).site_name);
 30    end loop;
 31  end;
 32  /

Procedure created.

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2    TEST_SITE_ADDR SITE_ADDR_OBJECT_ARRAY;
  3    TEST_SITE_ADDR_OP SITE_ADDR_OBJECT_ARRAY_OP;
  4  BEGIN
  5    -- Modify the code to initialize the variable
  6     TEST_SITE_ADDR :=
  7  SITE_ADDR_OBJECT_ARRAY(SITE_ADDR_OBJECT('ChI08',1121,'Ser_Rd','Waterloo','Black_Hawk_County','Iowa','US',507011,'42.4569376','-92.4082',0),
  8  SITE_ADDR_OBJECT('ChIbm0',1131,'Alton_Court','Eldersburg','Carroll_County','Maryland','US',217841,'39.4118','-76.933',2),
  9  SITE_ADDR_OBJECT('EjEJv',1141,'Pow_Lakes_Rd','Powhatan','Powha_County','Virginia','US',231391,'37.57711','-77.9927',1),
 10  SITE_ADDR_OBJECT('EiWNo',1151,'Essex_Road','Greenwich','Fairf_County','Connecticut','US',068311,'41.028098','-73.666',1),
 11  SITE_ADDR_OBJECT('ChI1E',1161,'Kild_Farm_Road','Cary','Wake_County','North_Carolina','US',275111,'35.7455127','-78.7803',0),
 12  SITE_ADDR_OBJECT('ChII8',1171,'Waky_Hollow_Court','Wappi_Falls','Dutchess_County','NY','US',125901,'41.5964825','-73.910970',3),
 13  SITE_ADDR_OBJECT('EiJs',1181,'Sleepy_Court','Ambler','Montg_County','Pennsylvania','US',190021,'40.2227951','-75.1971',2),
 14  SITE_ADDR_OBJECT('ChIIU',1191,'Sleepy_Court','Perth_Amboy','Middlesex_County','New_Jersey','US',088611,'40.50909','-74.2766',3),
 15  SITE_ADDR_OBJECT('ChIzs',1201,'West_Street','Park_Ridge','Cook_County','Illinois','US',600681,'42.017220','-87.86121',0),
 16  SITE_ADDR_OBJECT('ChI38',1211,'Sleepy_Court','Monroe','Orange_County','New_York','US',109501,'41.3078975','-74.20619',3));
 17    SITE_ADDR_INSERT_2(
 18      TEST_SITE_ADDR => TEST_SITE_ADDR,
 19      TEST_SITE_ADDR_OP => TEST_SITE_ADDR_OP
 20    );
 21    -- Modify the code to output the variable
 22    -- DBMS_OUTPUT.PUT_LINE('TEST_SITE_ADDR_OP = ' || TEST_SITE_ADDR_OP);
 23  END;
 24  /
vv_addr_id:1
successful
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "MCDONAC.SITE_ADDR_INSERT_2", line 25
ORA-06512: at line 17


SQL>
SQL>


So it was line 25, which is: test_site_addr_op(i).site_id:=V_site_no;

You need to initialize this array first, eg

test_site_addr_op := SITE_ADDR_OBJECT_ARRAY_OP();

and then extend it as required to store elements.

SQL> create or replace
  2  procedure SITE_ADDR_INSERT_2( test_site_addr in SITE_ADDR_OBJECT_ARRAY,
  3   test_site_addr_op out SITE_ADDR_OBJECT_ARRAY_OP)
  4    as
  5    V_site_no NUMBER;
  6    v_addr_id number;
  7    vv_addr_id number;
  8    v_site_name varchar2(50);
  9  begin
 10    test_site_addr_op := SITE_ADDR_OBJECT_ARRAY_OP();
 11
 12    for i in 1 .. test_site_addr.count
 13    LOOP
 14    V_site_no:=site_no.NEXTVAL;
 15    v_addr_id:=addr_id.NEXTVAL;
 16
 17    insert into address_test(Addr_id,addr_cus_id,street_name,town,county,sub_county_state_province,country,post_zip_code,lattude,longitude,accuracy_level)
 18    values (v_addr_id,test_site_addr(i).site_cus_id,test_site_addr(i).street_name,test_site_addr(i).town,test_site_addr(i).county,test_site_addr(i).sub_count
y_state_province,test_site_addr(i).country,
 19    test_site_addr(i).post_zip_code,test_site_addr(i).lattude,test_site_addr(i).longitude,test_site_addr(i).accuracy_level);
 20
 21    select Addr_id into vv_addr_id from address_test  where  post_zip_code=test_site_addr(i).post_zip_code and addr_cus_id=test_site_addr(i).site_cus_id;
 22
 23    DBMS_OUTPUT.PUT_LINE('vv_addr_id:'||vv_addr_id);
 24
 25    INSERT INTO SITE_TEST(site_id,site_name,site_cus_id,site_addr_id) VALUES(V_site_no,test_site_addr(i).site_name,test_site_addr(i).site_cus_id,vv_addr_id);

 26    v_site_name:=test_site_addr(i).site_name;
 27    DBMS_OUTPUT.PUT_LINE('successful');
 28    test_site_addr_op.extend;
 29    test_site_addr_op(i) := SITE_ADDR_OBJECT_OP ( V_site_no, v_site_name);
 30    DBMS_OUTPUT.PUT_LINE('site_id: '||test_site_addr_op(i).site_id);
 31    DBMS_OUTPUT.PUT_LINE('site_name: '||test_site_addr_op(i).site_name);
 32    end loop;
 33  end;
 34  /

Procedure created.

SQL>
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2    TEST_SITE_ADDR SITE_ADDR_OBJECT_ARRAY;
  3    TEST_SITE_ADDR_OP SITE_ADDR_OBJECT_ARRAY_OP;
  4  BEGIN
  5    -- Modify the code to initialize the variable
  6     TEST_SITE_ADDR :=
  7  SITE_ADDR_OBJECT_ARRAY(SITE_ADDR_OBJECT('ChI08',1121,'Ser_Rd','Waterloo','Black_Hawk_County','Iowa','US',507011,'42.4569376','-92.4082',0),
  8  SITE_ADDR_OBJECT('ChIbm0',1131,'Alton_Court','Eldersburg','Carroll_County','Maryland','US',217841,'39.4118','-76.933',2),
  9  SITE_ADDR_OBJECT('EjEJv',1141,'Pow_Lakes_Rd','Powhatan','Powha_County','Virginia','US',231391,'37.57711','-77.9927',1),
 10  SITE_ADDR_OBJECT('EiWNo',1151,'Essex_Road','Greenwich','Fairf_County','Connecticut','US',068311,'41.028098','-73.666',1),
 11  SITE_ADDR_OBJECT('ChI1E',1161,'Kild_Farm_Road','Cary','Wake_County','North_Carolina','US',275111,'35.7455127','-78.7803',0),
 12  SITE_ADDR_OBJECT('ChII8',1171,'Waky_Hollow_Court','Wappi_Falls','Dutchess_County','NY','US',125901,'41.5964825','-73.910970',3),
 13  SITE_ADDR_OBJECT('EiJs',1181,'Sleepy_Court','Ambler','Montg_County','Pennsylvania','US',190021,'40.2227951','-75.1971',2),
 14  SITE_ADDR_OBJECT('ChIIU',1191,'Sleepy_Court','Perth_Amboy','Middlesex_County','New_Jersey','US',088611,'40.50909','-74.2766',3),
 15  SITE_ADDR_OBJECT('ChIzs',1201,'West_Street','Park_Ridge','Cook_County','Illinois','US',600681,'42.017220','-87.86121',0),
 16  SITE_ADDR_OBJECT('ChI38',1211,'Sleepy_Court','Monroe','Orange_County','New_York','US',109501,'41.3078975','-74.20619',3));
 17    SITE_ADDR_INSERT_2(
 18      TEST_SITE_ADDR => TEST_SITE_ADDR,
 19      TEST_SITE_ADDR_OP => TEST_SITE_ADDR_OP
 20    );
 21    -- Modify the code to output the variable
 22    -- DBMS_OUTPUT.PUT_LINE('TEST_SITE_ADDR_OP = ' || TEST_SITE_ADDR_OP);
 23  END;
 24  /
vv_addr_id:41
successful
site_id: 41
site_name: ChI08
vv_addr_id:42
successful
site_id: 42
site_name: ChIbm0
vv_addr_id:43
successful
site_id: 43
site_name: EjEJv
vv_addr_id:44
successful
site_id: 44
site_name: EiWNo
vv_addr_id:45
successful
site_id: 45
site_name: ChI1E
vv_addr_id:46
successful
site_id: 46
site_name: ChII8
vv_addr_id:47
successful
site_id: 47
site_name: EiJs
vv_addr_id:48
successful
site_id: 48
site_name: ChIIU
vv_addr_id:49
successful
site_id: 49
site_name: ChIzs
vv_addr_id:50
successful
site_id: 50
site_name: ChI38

PL/SQL procedure successfully completed.

SQL>
SQL>


Rating

  (1 rating)

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

Comments

Many thanks.

Partha pratim Das, June 18, 2018 - 11:16 am UTC

Many thanks Connor McDonald ... its now working fine..
you genius.
Connor McDonald
June 19, 2018 - 3:21 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library