Home » SQL & PL/SQL » SQL & PL/SQL » Getting error :ORA-06531:Reference to unintialized collection (merged)
icon9.gif  Getting error :ORA-06531:Reference to unintialized collection (merged) [message #403035] Wed, 13 May 2009 09:35 Go to next message
RaviRajHulk
Messages: 7
Registered: April 2009
Junior Member
HI,
I have created a procedure whose output parameter is an array type.

The array is of object tpe
---------------

CREATE OR REPLACE TYPE SAT_OBJ_TYPE IS OBJECT
(
AMOUNT NUMBER (15,2),
MONTH_VAL NUMBER (10)
)

CREATE OR REPLACE TYPE SAT_TABTYPE AS TABLE OF SAT_OBJ_TYPE

-------------------------------
The procedure when executed gives error as ORA-06531:Reference to unintialized collection . The procedure is given below

CREATE OR REPLACE PROCEDURE test(
v_id IN VARCHAR2,
v_sat_array OUT sat_tabtype
)
IS
v_sat_obj sat_obj_type;
v_count NUMBER;

--Declaration of cursor
CURSOR c1
IS
SELECT test_type, time_id, amount_value,sub_test_type
FROM test_table
WHERE test_id = v_id
ORDER BY time_id;
BEGIN
--Declaration of variables

SELECT COUNT (*)
INTO v_count
FROM test_table
WHERE itr_id = v_id AND test_type = 'AT' and test_type = 'IT' and time_id = 1;

FOR loop_index1 IN c1
LOOP
IF (loop_index1.test_type = 'IT') AND (loop_index1.sub_test_type = 'AT')
THEN
FOR i IN 0..v_count
LOOP
v_sat_obj := v_sat_array (i);
v_sat_obj.amount := loop_index1.amount_value;
v_sat_obj.MONTH1 := loop_index1.time_id;
END LOOP;
END IF;
END LOOP;
END ;
/
Re: Getting error :ORA-06531:Reference to unintialized collection [message #403044 is a reply to message #403035] Wed, 13 May 2009 09:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your code doesn't make a lot of sense - v_sat_array is defined as an out parameter, but you seem to be trying to unpack values from v_sat_array into v_sat_obj:
v_sat_obj := v_sat_array (i);
 v_sat_obj.amount := loop_index1.amount_value;
 v_sat_obj.MONTH1 := loop_index1.time_id;


This will be where your error comes from, as you never initialise v_sat_obj before you use it

Re: Getting error :ORA-06531:Reference to unintialized collection [message #403045 is a reply to message #403035] Wed, 13 May 2009 10:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
CREATE OR REPLACE PROCEDURE Test
     (v_id         IN VARCHAR2,
      v_sat_array  OUT SAT_TABTYPE)
IS
  v_sat_obj  SAT_OBJ_TYPE;
  v_count    NUMBER;
  --Declaration of cursor
  CURSOR c1 IS
    SELECT   test_type,
             time_id,
             amount_value,
             sub_test_type
    FROM     test_table
    WHERE    test_id = v_id
    ORDER BY time_id;
BEGIN
  --Declaration of variables
  SELECT Count(* )
  INTO   v_count
  FROM   test_table
  WHERE  itr_id = v_id
         AND test_type = 'AT'
         AND test_type = 'IT'
         AND time_id = 1;
  
  FOR loop_index1 IN c1 LOOP
    IF (loop_index1.test_type = 'IT')
       AND (loop_index1.sub_test_type = 'AT') THEN
      FOR i IN 0.. v_count LOOP
        v_sat_obj := V_sat_array(i);
        
        v_sat_obj.amount := loop_index1.amount_value;
        
        v_sat_obj.month1 := loop_index1.time_id;
      END LOOP;
    END IF;
  END LOOP;
END;
/ 

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
icon9.gif  Getting error :ORA-06531:Reference to unintialized collection [message #403140 is a reply to message #403035] Wed, 13 May 2009 23:16 Go to previous messageGo to next message
RaviRajHulk
Messages: 7
Registered: April 2009
Junior Member
Hi,
I have created a Stored procedure which return an array as the out parameter. While executing the Procedure , I am getting the following error----ORA-06530: Reference to uninitialized composite

The array is of object type
CREATE OR REPLACE TYPE ITRSCHEMA1.ITR1_TDS_ON_SALARY_OBJ_TYPE IS OBJECT
(
AMOUNT NUMBER (15,2),
MONTH1 NUMBER (10)
);


CREATE OR REPLACE TYPE ITRSCHEMA1.TDS_ON_SALARY_TABTYPE AS TABLE OF ITR1_TDS_ON_SALARY_OBJ_TYPE


-----
The stored procedure is as following:

CREATE OR REPLACE PROCEDURE test(
v_id IN VARCHAR2,
v_sat_array OUT sat_tabtype
)
IS
v_sat_obj sat_obj_type;
v_count NUMBER;

--Declaration of cursor
CURSOR c1
IS
SELECT test_type, time_id, amount_value,sub_test_type
FROM test_table
WHERE test_id = v_id
ORDER BY time_id;
BEGIN
--Declaration of variables

SELECT COUNT (*)
INTO v_count
FROM test_table
WHERE itr_id = v_id AND test_type = 'AT' and test_type = 'IT' and time_id = 1;

FOR loop_index1 IN c1
LOOP
IF (loop_index1.test_type = 'IT') AND (loop_index1.sub_test_type = 'AT')
THEN
FOR i IN 0..v_count
LOOP
v_sat_obj := v_sat_array (i);
v_sat_obj.amount := loop_index1.amount_value;
v_sat_obj.MONTH1 := loop_index1.time_id;
END LOOP;
END IF;
END LOOP;
END ;
/
Re: Getting error :ORA-06531:Reference to unintialized collection [message #403144 is a reply to message #403140] Wed, 13 May 2009 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/403035/136107/#msg_403035

Do NOT cross/multi-post!


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Getting error :ORA-06531:Reference to unintialized collection [message #403145 is a reply to message #403035] Wed, 13 May 2009 23:20 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/403035/136107/#msg_403035

Do NOT cross/multi-post!


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Getting error :ORA-06531:Reference to unintialized collection (merged) [message #403147 is a reply to message #403035] Wed, 13 May 2009 23:26 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/403035/136107/#msg_403035

Do NOT cross/multi-post!


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Previous Topic: Handling Rejected Rows
Next Topic: how can i write regular expression for this
Goto Forum:
  


Current Time: Sun Dec 04 16:35:58 CST 2016

Total time taken to generate the page: 0.09899 seconds