Forum Stats

  • 3,827,745 Users
  • 2,260,818 Discussions
  • 7,897,364 Comments

Discussions

ORA-06531: Reference to uninitialized collection

Lokesh A
Lokesh A Member Posts: 12 Red Ribbon

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

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    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.

    Lokesh A

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,427 Gold Trophy
    edited May 11, 2022 11:36AM

    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
    BEDE Oracle Developer Member Posts: 2,427 Gold Trophy

    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.

    Lokesh A
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    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.

    Lokesh A