PL/SQL Help needed

From: Dave Rabkin <darabkin_at_ameritech.net>
Date: Tue, 15 Aug 2000 23:32:37 -0500
Message-ID: <4Pom5.33544$E05.515872@nntp0.chicago.il.ameritech.net>

I've been able to a return a ref cursor that I filled with a select statement to my active server page using oo4o. But now I need to build a multi-column, multi-row table within my package, one row at a time using IF statements.

I've defined a record type, set a record to that record type and set a ref cursor to that record using the <record-name>%TYPE format. Then I set an output parameter for my procedure to that cursor type.

But when I try to reference a column in a particular row:

        "Performance_Rating_Out(Local_Row_Index).Performace_Rating_Name := 'E = Exceeds';",
PL/SQL thinks I'm referring to a function(PLS-00222: no function with name "Performance_Rating_Out" exists in this scope).

Here's the code. I've indicated the line with "====>" where the first error occurs.

create or replace package Performance_Rating_Dist is

    TYPE Performance_Rating_Record is RECORD
         (Performace_Rating                number(2,3),
          Performace_Rating_Name     varchar2(30),
          Performace_Rating_Target    number(2,3));

    v_Performance_Rating_Rec Performance_Rating_Record;

    TYPE Performance_Rate_Cursor IS REF CURSOR return v_Performance_Rating_Rec%type;

    Procedure Performance_Rating_Data(session_id in varchar2,

Performance_Rating_Out OUT Performance_Rate_Cursor);

end Performance_Rating_Dist;

create or replace package body Performance_Rating_Dist is

      Procedure Performance_Rating_Data(session_id in varchar2,

Performance_Rating_Out OUT Performance_Rate_Cursor) is

      Local_Row_Index            binary_integer :=1;
      Other_total                       number(8,2);
      Total                                 number(8,2);

      CURSOR Performance_Rating_Cursor IS
          SELECT tblemployees.PerformanceRating,
                 Count(tblemployees.PerformanceRating) as
Count_of_Performance_Rating
          FROM tblemployees, tblfilter
          WHERE tblemployees.ProrationPercent > 0 and
                tblemployees.departmentid = tblfilter.departmentid and
                tblemployees.delete_ind = 'N' and
                tblemployees.PerformanceRating is not null and
                tblemployees.PERFORMANCERATING != 'T' and
                tblfilter.sessionid = session_id
          GROUP BY tblemployees.PerformanceRating;

      BEGIN
          SELECT Count(tblemployees.name)
          INTO Total
          FROM tblemployees, tblfilter
          WHERE tblemployees.ProrationPercent > 0 and
                tblemployees.departmentid = tblfilter.departmentid and
                tblemployees.PERFORMANCERATING != 'T' and
                tblfilter.sessionid = session_id;

          For v_Performance_Rating_Record in Performance_Rating_Cursor
              Loop
                 If v_Performance_Rating_Record.PerformanceRating = 'E' Then

====>

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Name := 'E = Exceeds';

Performance_Rating_Out(Local_Row_Index).Performace_Rating := v_Performance_Rating_Record.Count_of_Performance_Rating / Total;

                     Other_total := Other_total -

v_Performance_Rating_Record.Count_of_Performance_Rating;

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Target := .2;

                 ElsIf v_Performance_Rating_Record.PerformanceRating = 'F'

Then

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Name := 'F = Fully Meets';

Performance_Rating_Out(Local_Row_Index).Performace_Rating := v_Performance_Rating_Record.Count_of_Performance_Rating / Total;

                     Other_total := Other_total -

v_Performance_Rating_Record.Count_of_Performance_Rating;

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Target := .5;

                 ElsIf v_Performance_Rating_Record.PerformanceRating = 'M'

Then

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Name := 'M = Minimally Meets';

Performance_Rating_Out(Local_Row_Index).Performace_Rating := v_Performance_Rating_Record.Count_of_Performance_Rating / Total;

                     Other_total := Other_total -

v_Performance_Rating_Record.Count_of_Performance_Rating;

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Target := .2;

                 ElsIf v_Performance_Rating_Record.PerformanceRating = 'D'

Then

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Name := 'D = Does not Meet';

Performance_Rating_Out(Local_Row_Index).Performace_Rating := v_Performance_Rating_Record.Count_of_Performance_Rating / Total;

                     Other_total := Other_total -

v_Performance_Rating_Record.Count_of_Performance_Rating;

Performance_Rating_Out(Local_Row_Index).Performace_Rating_Target := .1;

                 End If;

                 Local_Row_Index :=Local_Row_Index + 1;
              END LOOP;

      EXCEPTION
          WHEN others THEN
               rollback ;
      END;

end Performance_Rating_Dist; Received on Tue Aug 15 2000 - 23:32:37 CDT