How to pass programmer-defined table to calling program

From: Dave Rabkin <darabkin_at_ameritech.net>
Date: Mon, 14 Aug 2000 09:33:44 -0500
Message-ID: <KqTl5.21023$E05.342440@nntp0.chicago.il.ameritech.net>

I want to pass a table/cursor(I'm not sure which) back to my ASP application from a package. The table/cursor would be defined in my package, filled by logic in my package from queries in my package. I get the message back from ASP that:

Oracle Automation error '800a01b8'

SQL execution error, ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EXCEPTION_REPORT_RECORDS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I've never tried to return a table to an ASP, just a cursor filled by a select. I tried using a cursor instead of a table but I get compile errors. Here's code I've written passing a table out that compiles but doesn't execute:

create or replace package Approval_Report_Exceptions is

  TYPE Exception_Record is RECORD

       (reportinglevel         tblmanagers.reportinglevel%TYPE,
        employee_number          tblemployees.name%TYPE,
        employee_name          tblemployees.name%TYPE,
        exception_explanation  varchar2(100),
        severity_level         varchar2(1)
       );

  TYPE Exception_Table_Data IS TABLE OF Exception_Record INDEX BY BINARY_INTEGER;  Procedure Exception_Report_Records(session_id in varchar2,

                                     Exception_Table_Out OUT

Exception_Table_Data);

end Approval_Report_Exceptions;

create or replace package body Approval_Report_Exceptions is

  Procedure Exception_Report_Records(session_id in varchar2,

                                     Exception_Table_Out OUT

Exception_Table_Data) IS

       Local_Row_Index            binary_integer :=1;
       Local_TotalPayoutBefore    number(9);
       Local_TotalPayoutAfter     number(9);
       Local_Plan_Year            date;
       Local_Too_New_Date         date;

       CURSOR Employee_Data_Cursor IS
            SELECT TBLEMPLOYEES.costcenterrollup, TBLEMPLOYEES.NAME,
                   TBLEMPLOYEES.PERFORMANCERATING, TBLEMPLOYEES.SALARYEND,
                   TBLEMPLOYEES.individualmultiplier, TBLEMPLOYEES.lumpsum,
                   TBLEMPLOYEES.lumpsumcomments, TBLEMPLOYEES.maximum,
                   TBLEMPLOYEES.increase_dollars,
TBLEMPLOYEES.increasepercent,
                   TBLEMPLOYEES.hiredate, TBLEMPLOYEES.employeenumber
            FROM TBLEMPLOYEES, tblfilter
            WHERE tblfilter.sessionid = session_id and
                  TBLEMPLOYEES.departmentid  = tblfilter.departmentid and
                  TBLEMPLOYEES.delete_ind = 'N'
            ORDER BY TBLEMPLOYEES.costcenterrollup, TBLEMPLOYEES.NAME;

    BEGIN
         SELECT Sum(salaryend),
                Sum(newsalary)
         INTO   Local_TotalPayoutBefore,
                Local_TotalPayoutAfter
         FROM tblemployees, tblfilter
         WHERE tblemployees.departmentid = tblfilter.departmentid and
               tblfilter.sessionid = Session_ID;

         SELECT plan_year
         INTO Local_Plan_Year
         FROM tblparameters;

         Local_Too_New_Date := TO_DATE(('10/01/' || Local_Plan_Year),

'MMDDYYYY');

         If not employee_Data_cursor%ISOPEN then
            OPEN employee_Data_cursor;
         End If;

         For Employee_Data_Record in employee_Data_cursor
         Loop
            If Employee_Data_Record.PERFORMANCERATING IS Null then
                Exception_Table_Out(Local_Row_Index).reportinglevel :=
Employee_Data_Record.costcenterrollup;
                Exception_Table_Out(Local_Row_Index).employee_name :=
Employee_Data_Record.NAME;
                Exception_Table_Out(Local_Row_Index).exception_explanation
:= 'Missing Performance Rating';
                Exception_Table_Out(Local_Row_Index).severity_level := 'c';

                Local_Row_Index := Local_Row_Index + 1;
            END IF;

         End Loop;

     EXCEPTION
         WHEN others THEN
              rollback ;
     END;

END Approval_Report_Exceptions; Received on Mon Aug 14 2000 - 09:33:44 CDT