SQL & PL/SQL

Oracle 10.2.0.5.0
Using Pl/SQL Developer

Hi I'm new to collections, object types etc, so I aologize for any poor wording and missed concepts...

I need to output a ref cursor from my package (for a summary report in SQL Server Reporting Services 2005). The summary report has two fields that come from the database table and 5 calculated fields. My idea for creating the ref cursor is as follows:

1. Define an object type at the schema level
2. Define a table (collection) type at the schema level
3. Define a ref cursor at the package level
4. Using dynamic SQL create a sql statement creating virtual columns for the 5 calculated fields
5. Fetch cursor with dynamic sql into object type one record at a time
6. Calculate the other five field values and update the object for each record processed
7. Add the object 'record' to the table (collection) after each record is processed
8. After the fetch is complete, convert the table to a ref cursor for returning to my application

Here is what I have so far. I have cut out several of the calculated fields for simplicities sake. It is not complete and I don't know how to fetch the database row into the object, nor convert the collection to a ref cursor.

Any help would be greatly appreciated.

create or replace type dlyout.srvCtr_sum_rec_type as object (
                           zoneNo number,
                           zonetx varchar2(15),
                           distNo varchar2(4),
                           distTx varchar2(30),
                           numOccr number,
                           MEError varchar2(1));

create or replace type dlyout.srvCtr_sum_tbl_of_recs is table of srvCtr_sum_rec_type;

CREATE OR REPLACE PACKAGE DLYOUT.REPORTS_PKG is
  TYPE CUR IS REF CURSOR;  

PROCEDURE testABC(startDate IN date,
                       endDate IN date,
                       startTime IN varchar2,
                       endTime IN varchar2,
                       zoneNo IN dlyout.loc.zone_no%type,
                       distNo IN dlyout.loc.dist_cd%type,
                       CUROUT OUT CUR);
                       
END;


CREATE OR REPLACE PACKAGE BODY DLYOUT.REPORTS_PKG IS
  PROCEDURE testABC(startDate IN date,
                       endDate IN date,
                       startTime IN varchar2,
                       endTime IN varchar2,
                       zoneNo IN dlyout.loc.zone_no%type,
                       distNo IN dlyout.loc.dist_cd%type,
                       CUROUT OUT CUR) as
  
           
  startDateTimeStr varchar2(10) := to_Char(startDate, 'MM/DD/YYYY') ;      
  endDateTimeStr varchar2(10) := to_Char(endDate, 'MM/DD/YYYY');    
    
  startDateTime date := to_date(startDateTimeStr || ' ' || startTime, 'MM/DD/YYYY HH24:MI:SS');
  endDateTime date := to_date(endDateTimeStr|| ' ' || endTime, 'MM/DD/YYYY HH24:MI:SS');
  
  distClause varchar2(1000);
  sqls varchar2(2000);
  zoneClause varchar2(1000) :='';
  idx number :=0;
  
  curSrvCtr cur;

  srvCtrRec srvCtr_sum_rec_type;
  srvCtrTbl srvCtr_sum_tbl_of_recs :=srvCtr_sum_tbl_of_recs();
  
    
  BEGIN
    
    if zoneNo <> 9999 then
        zoneClause := ' and zone_no member of dlyout.reports_common_stuff_pkg.convert_to_collection(zoneNo)';
    end if;
    
    if distNo <> '9999' then
        distClause := ' and dist_cd member of dlyout.reports_common_stuff_pkg.convert_to_collection(distNo) ';
    end if;
    
    sqls := 'select distinct l.zone_no zoneNo, l.zone_tx zoneTx, 
            l.dist_cd distCd , l.dist_tx distTx, 0 numOccr, '''' MEError        
            from dlyout.loc l
            where l.ts between  :startts and :endts ' ||
            zoneClause ||
            distClause ||
            ' order by l.zone_no, l.dist_tx ';
    
     
    open curSrvCtr for sqls using  startDateTime, endDateTime; 
    LOOP
      FETCH curSrvCtr INTO srvCtrRec;      --ORA:00932 inconsistent datatype expected - got - 
      EXIT WHEN curSrvCtr%NOTFOUND;
        --call other functions to get calculated fields
        srvCtrRec.numOccr := dlyout.reports_common_stuff_pkg.Num_Loc_Exc_Mom(startDateTimeStr, endDateTimeStr, starttime, endTime, srvctrRec.distno);
        srvCtrRec.MEError := dlyout.reports_common_stuff_pkg.ME_Error(startDateTimeStr, endDateTimeStr, starttime, endTime, srvCtrRec.distNo, null);
        dbms_output.put_line(srvCtrRec.distTx || ' ' || srvCtrRec.numoccr|| ' ' || srvCtrRec.MEError);
     end loop;
      
end testABC;
END;

  

Then I need to add the object to the table. Something like this?

       -- add object 'record' to table
       srvCtrTbl.extend;
       srvCtrTbl.last := srvCtrRec;
 

Then I am not sure how to do the cast to get the table to a ref cursor. Something like this?

open curout for SELECT *
 FROM TABLE (CAST (srvCtrTbl AS srvCtr_sum_tbl_of_recs))
ORDER BY zoneNo, distTx;