SQL & PL/SQL

Hello All,

I'm attempting to write a PL/SQL statement over some very large tables. The processing of the statement takes over 45 minutes to complete.

I've been able to speed up processing by creating a temp table over the largest of the tables, and it truly has speed up processing. The problem is that this statement will be used in a report run from SQL Reporting Services, using PL/SQL. This will be used in various reports and would like to make this an adhoc type of job - launched by executing the report.

I've tried writing a procedure that returns a recordset, but I'm having no luck. I'm new to writing Stored Procedures, Functions and Packages, and am trying to get my head around the best way to approach this.

Here is what I have so far - and it's not even close to working, but you can see the logic I'm trying to follow as far was what I need to return.

CREATE OR REPLACE PACKAGE PKG_BillSegs_ZeroUsage AS
TYPE cursor_type IS REF CURSOR;
Procedure GETBILLSEG_ZEROUSAGE (
p_RevMth IN bi_bill_segment.rev_month%TYPE,
p_recordset OUT PKG_BillSegs_ZeroUsage.cursor_type);

END PKG_BillSegs_ZeroUsage;

CREATE OR REPLACE PROCEDURE GetBillSeg_ZeroUsage (p_RevMth IN bi_bill_segment.rev_month%TYPE,
p_recordset OUT PKG_BillSegs_ZeroUsage.cursor_type) AS
BEGIN
OPEN p_recordset FOR
select * from bi_bill_segment where usage_qty = 0 and rev_month = p_RevMth;
END GetBillSeg_ZeroUsage;
/


Any help is greatly appreciated!