ORA-06531: Reference to uninitialized collection

Lokesh A

set SERVEROUTPUT ON

Declare

type t is table of Varchar2(6);

v_t1 t:= t();--initialized collection variable

Begin

v_t1.extend(6);

v_t1(1):='Lokesh';

v_t1(2):='Ashok';

v_t1(3):='Mahesh';

v_t1(4):='Ramu';

v_t1(5):='Ajay';

v_t1(6):='Irani chawla';

for i in v_t1.first..v_t1.last loop

dbms_output.put_line('Hi v_t1='||v_t1(i));

end loop;

forall i in v_t1.first..v_t1.last save exceptions

insert into temp99 values(v_t1(i));

Exception when others then

dbms_output.put_line('bulk exception count='||sql%bulk_exceptions.count);

for i in 1..sql%bulk_exceptions.count loop

dbms_output.put_line('bulk exception index='||sql%bulk_exceptions(i).error_index);

dbms_output.put_line('bulk exception code='||sql%bulk_exceptions(i).error_code);

end loop;

End;

My table structure is

create table temp99(tname Varchar2(6))

Getting error like

Error report -

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 22

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 12

06531. 00000 - "Reference to uninitialized collection"

*Cause:  An element or member function of a nested table or varray

      was referenced (where an initialized collection is needed)

      without the collection having been initialized.

*Action:  Initialize the collection with an appropriate constructor

      or whole-object assignment.

I want to use error index and error code, so my table structure is varchar2(6) only.

If i am removing v_t1(6):='Irani chawla'; collection vallue then executing fine but when i am using

v_t1(6):='Irani chawla'; collection value facing same issue. please suggest i went through google also but not getting any idea

Frank Kulash

HI, @Lokesh A

The error message:

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 22

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 12

says the real error occurred at line 12. SQL%BULK_EXCEPTIONS is initialized at line 22, when you say SAVE EXCEPTIONS . If you change line 2 to

  type t is table of Varchar2(20);

then no error will occur before line 22, so when an error does occur in the INSERT statement on line 23, SQL%BULK_EXCEPTIONS can catch it.

BEDE

v_t1(6):='Irani chawla'; will raise an exception. But that is not a bulk exception.

So, all that is because of poor exception handling.

Try the below and see:

Declare
type t is table of Varchar2(6);
v_t1 t:= t();--initialized collection variable
Begin
  v_t1.extend(6);
  v_t1(1):='Lokesh';
  v_t1(2):='Ashok';
  v_t1(3):='Mahesh';
  v_t1(4):='Ramu';
  v_t1(5):='Ajay';
  v_t1(6):='Irani chawla';
  for i in v_t1.first..v_t1.last loop
     dbms_output.put_line('Hi v_t1='||v_t1(i));
  end loop;
  begin
  forall i in v_t1.first..v_t1.last save exceptions
    insert into temp99 values(v_t1(i));
  exception
    when others then 
      dbms_output.put_line('bulk exception count='||sql%bulk_exceptions.count);
      for i in 1..sql%bulk_exceptions.count loop
      dbms_output.put_line('bulk exception index='||sql%bulk_exceptions(i).error_index);
      dbms_output.put_line('bulk exception code='||sql%bulk_exceptions(i).error_code);
      end loop;    
  end;
/*
Exception when others then


  dbms_output.put_line('bulk exception count='||sql%bulk_exceptions.count);
  for i in 1..sql%bulk_exceptions.count loop
  dbms_output.put_line('bulk exception index='||sql%bulk_exceptions(i).error_index);
  dbms_output.put_line('bulk exception code='||sql%bulk_exceptions(i).error_code);
  end loop;
 */ 
End;

By increasing the size of that element you will run the script successfully.

Thus, try:

create table temp99(tname Varchar2(100));

Declare
type t is table of temp99.tname%type;
v_t1 t:= t();--initialized collection variable
Begin
  v_t1.extend(6);
  v_t1(1):='Lokesh';
  v_t1(2):='Ashok';
  v_t1(3):='Mahesh';
  v_t1(4):='Ramu';
  v_t1(5):='Ajay';
  v_t1(6):='Irani chawla';
  for i in v_t1.first..v_t1.last loop
     dbms_output.put_line('Hi v_t1='||v_t1(i));
  end loop;
  begin
  forall i in v_t1.first..v_t1.last save exceptions
    insert into temp99 values(v_t1(i));
  exception
    when others then 
      dbms_output.put_line('bulk exception count='||sql%bulk_exceptions.count);
      for i in 1..sql%bulk_exceptions.count loop
      dbms_output.put_line('bulk exception index='||sql%bulk_exceptions(i).error_index);
      dbms_output.put_line('bulk exception code='||sql%bulk_exceptions(i).error_code);
      end loop;    
  end;
/*
Exception when others then


  dbms_output.put_line('bulk exception count='||sql%bulk_exceptions.count);
  for i in 1..sql%bulk_exceptions.count loop
  dbms_output.put_line('bulk exception index='||sql%bulk_exceptions(i).error_index);
  dbms_output.put_line('bulk exception code='||sql%bulk_exceptions(i).error_code);
  end loop;
 */ 
End;

BEDE

Normally, you should refer sql%bulk_exceptions only after forall was run. sql%bulk_exceptions is not initialized unless that forall is executed. So, the exception that occurred before the forall passed the control to the exception handler at the very end of the code before having executed the forall and thus found sql%bulk_exceptions collection not initialized.

Solomon Yakobson

SAVE EXCEPTION alone doesn't initialize collection sql%bulk_exceptions. It is initialized only if there were FORALL exceptions. Add the following to declaration section:

    DML_ARRAY_ERRORS EXCEPTION;  
    PRAGMA EXCEPTION_INIT (DML_ARRAY_ERRORS, -24381);  

And change EXCEPTIONS clause to:

exception
  when DML_ARRAY_ERRORS
    then
     dbms_output.put_line('bulk exception count='||sql%bulk_exceptions.count);
     for i in 1..sql%bulk_exceptions.count loop
       dbms_output.put_line('bulk exception index='||sql%bulk_exceptions(i).error_index);
       dbms_output.put_line('bulk exception code='||sql%bulk_exceptions(i).error_code);
     end loop;

SY.

Frank Kulash

HI, @Lokesh A

The error message:

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 22

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 12

says the real error occurred at line 12. SQL%BULK_EXCEPTIONS is initialized at line 22, when you say SAVE EXCEPTIONS . If you change line 2 to

  type t is table of Varchar2(20);

then no error will occur before line 22, so when an error does occur in the INSERT statement on line 23, SQL%BULK_EXCEPTIONS can catch it.