SQL & PL/SQL

Hi,

I have following code -

SQL> create Type Array_Cols Is Table of Varchar2(50);

SQL> create Type Assoc_Array Is Table of Array_Cols;

Create or Replace Package Forecast_Semi_Report
Is
Forecast_Array Assoc_Array := Assoc_Array();

Type Ref_Cursor is Ref Cursor;

Procedure Generate_Array(p_year IN Number, p_unit IN Varchar2, p_Ref_cur OUT Ref_Cursor);

End;


Create or Replace Package Body Forecast_Semi_Report
As
Procedure Generate_Array(p_year IN NUMBER, p_unit IN Varchar2,p_Ref_cur OUT Ref_Cursor)
As
Forecast_Array Assoc_Array := Assoc_Array();
v_String Varchar2(32767);

Cursor Cur_Year(p_year Number)
Is
Select Distinct(Year) As Year
From Forecast_Semi
Where Serial_No = (Select Min(Serial_No) From Forecast_Semi_Cagr Where Year = p_year)
Order By Year;

Cursor Cur_Serial(p_year Number)
IS
Select Serial_No, Segment, Cagr1, Cagr2
From Vlsiweb.Forecast_Semi_Cagr
Where Year = p_year
And Datatype = p_unit;

v_Cur_Serial_Value Number:=0;

Cursor Cur_Sales(v_Cur_Serial_Value Number)
Is
Select Sales, Year
From Vlsiweb.Forecast_Semi
Where Serial_No = v_Cur_Serial_Value
Order By Year;

i Number:=1;
j Number:=2;

out_file utl_file.file_type;

Ref_Cur Ref_Cursor;

Begin
out_file := utl_file.fopen('VLSI_DATA','Forecast_Semi_Output.csv','W');
Forecast_Array := Assoc_Array();
Forecast_Array.Extend(1);
Forecast_Array(1)(i) := 'SEGMENT';
i := i + 1;
For Cur_Temp1 in Cur_Year(p_year)
Loop
Forecast_Array(1)(i) := Cur_Temp1.Year;
i := i + 1;
End Loop;
Forecast_Array(1)(i) := 'CAGR1';
Forecast_Array(1)(i+1) := 'CAGR2';
i := 2;
Forecast_Array.Extend(1);
For Cur_Temp1 in Cur_Serial(p_year)
Loop
Forecast_Array(i)(1) := Cur_Temp1.Segment;
For Cur_Temp2 in Cur_Sales(cur_temp1.Serial_No)
Loop
Forecast_Array(i)(j) := Cur_Temp2.Sales;
j := j + 1;
End Loop;
Forecast_Array(i)(j) := Cur_Temp1.Cagr1;
j := j + 1;
Forecast_Array(i)(j) := Cur_Temp1.Cagr2;
j := 2;
i := i + 1;
Forecast_Array.Extend(1);
End Loop;
i := 0;
j := 0;
For i in 1.. Forecast_Array.count
Loop
For j in Forecast_Array(i).First.. Forecast_Array(i).Last
Loop
--Dbms_Output.Put_Line('Element ('||i||','||j||') - '||Forecast_Array(i)(j));
v_String := v_String || Forecast_Array(i)(j)||',';
End Loop;
utl_file.put(out_file,v_String);
v_string := Null;
utl_file.new_line(out_file,2);
End Loop;
utl_file.fclose(out_file);

Open Ref_Cur For
Select *
From Table(Cast(Forecast_Array As Assoc_Array));

Dbms_Output.Put_Line('Finished!');

Exception When Others Then
utl_file.fclose(out_file);
Dbms_Output.Put_Line(SQLERRM(SQLCODE));
End;
End;


And to execute above package, I use following code,

DECLARE
P_YEAR NUMBER;
P_UNIT VARCHAR2(200);
P_REF_CUR FORECAST_SEMI_REPORT.Ref_Cursor;
BEGIN
P_YEAR := 2008;
P_UNIT := 'DOLLAR';
VLSIWEB.FORECAST_SEMI_REPORT.GENERATE_ARRAY ( P_YEAR, P_UNIT, P_REF_CUR );
END;


And I am getting following output -

SQL> set serverout on;
SQL> Declare
2 Ref_Cur_Temp Forecast_Semi_Report.Ref_Cursor;
3 Begin
4 Forecast_Semi_Report.Generate_Array(2008, 'DOLLAR', Ref_Cur_Temp);
5 Exception When others then
6 dbms_output.put_line(SQLERRM(SQLCODE));
7 End;
8 /
ORA-06531: Reference to uninitialized collection

PL/SQL procedure successfully completed.


Can anyone provide anykind of solution on this?

I have read rest of the posts in this forum and some googling..

But couldnt get any help.

I tried initializing the Forecast_Array at time of declaration only as -

Forecast_Array Assoc_Array := Assoc_Array('A');

But it also didnt help.


Thanks in advnace!

Avinash.