This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 3 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

This is default featured slide 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions..

 

Thursday, February 28, 2013

ORA-06530: Reference to uninitialized composite

ORA-06530: Reference to uninitialized composite
ORA-06512: at "PROCEDURE/PACKAGE", line 88
ORA-06512: at "PROCEDURE/PACKAGE", line 446
ORA-06512: at line 11
06530. 00000 -  "Reference to uninitialized composite"
*Cause:    An object, LOB, or other composite was referenced as a
           left hand side without having been initialized.
*Action:   Initialize the composite with an appropriate constructor
           or whole-object assignment.

Cause:

This error would be found when using oracle collection table type, and trying to assign the value to the collect. When compiling the code, it would be fine. But, when execute the package/procedure, you will have this error message.

The error caused when trying to extend the define table type object without being initialized. See the scenario below:

CREATE TYPE obj_test AS OBJECT
       (tst_id       number,
        tst_name    VARCHAR2(50));
/
CREATE TYPE tab_test AS TABLE OF obj_test;
/

declare
      l_tab_tst tab_test := tab_test();  
begin
    begin
        l_tab_tst.extend;
        l_tab_tst(l_tab_tst.last).tst_id := 22;
        l_tab_tst(l_tab_tst.last).tst_name :=  'TEST';
    exception
    when no_data_found then
       null;
    end;
end;
/

Solution:

To fix this, simply add initialized object line as shown below:

declare
      l_tab_tst tab_test := tab_test();  
begin
    begin
        l_tab_tst.extend;
        l_tab_tst(l_tab_tst.last) := type_supplier(NULL, NULL);

        l_tab_tst(l_tab_tst.last).tst_id := 22;
        l_tab_tst(l_tab_tst.last).tst_name :=  'TEST';
    exception
    when no_data_found then
       null;
    end;
end;
/

ORA-06052: PL/SQL: numeric or value error on PL/SQL Collections First and Last

Cause:
The error ORA-06052: PL/SQL: numeric or value error would be found when using for loop on the type collection object, as shown below, and the collection is empty:

FOR i IN type_variable.FIRST..t_variable.LAST LOOP
     ----your code
End Loop ;

When the collection is not empty, error ORA-06052: PL/SQL: numeric or value error would not be found.
This is because FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.


Solution:

Use  FOR i IN 1..t_variable.count instead of FOR i IN type_variable.FIRST..t_variable.LAST , and also apply exception block.

BEGIN
      FOR i IN 1..t_variable.count
           ----your code
      End Loop;
EXCEPTION WHEN OTHERS
     -- error handling code
END;