Need help

From: Daman <dlaw2_at_gl.umbc.edu>
Date: Thu, 10 Feb 2000 11:26:45 -0500
Message-ID: <Pine.LNX.4.10A.B3.10002101125430.29462-100000@jabba.gl.umbc.edu>

Hi,

 Thank you for your time.
 I have a Perl/Oracle DBI script for the web which output the records (in  table format) of all tickets with certain status. What I want to do is  to break this page (contains only one table) into multiple pages. For  example, the query returns 1,000 closed tickets, and I want to have 10  pages. First page has 1-100 records, second page has 101-200 records,  and so on.

 Below is a sample of my test script. The procedure gave the correct  output, but when parameters are passed back to the calling procedure, it  returned blank or null. Is this because a procedure can only return a  single record or result? Am I on the right track with this script?

 Please provide any feedbacks or suggestions.

 Thanks again for your time, I greatly appreciated.

---

/*
This Procedure sets a page break.
p_lower is the lower bound for each page. */

CREATE OR REPLACE PROCEDURE PROC_PG_BREAK

                (p_lname OUT VARCHAR2, p_ticket_id OUT NUMBER, p_lower IN
NUMBER, p_status IN NUMBER) AS
        CURSOR cur_ticket IS
                SELECT    Customer_Last_Name, First_Name, Ticket_ID
                FROM      Service_Request
                WHERE     Status = p_status
                ORDER BY  UPPER(Customer_Last_Name), UPPER(First_Name),

Ticket_ID DESC;

        v_counter INTEGER(7) := 0;
        v_lower NUMBER(3) := p_lower;
        v_upper NUMBER(4) := v_lower + 100;
        v_total NUMBER(7);

BEGIN
        SELECT  COUNT(*)
        INTO        v_total
        FROM      IC__Service_Request;

        FOR v_counter IN v_lower..v_upper LOOP
                IF (v_lower < v_upper) THEN
                        p_total := v_total;
                        p_lname := rec_ticket.Customer_Last_Name;
                        p_ticket_id := rec_ticket.Ticket_ID
                END IF;
        v_lower := v_lower + 1;
        END LOOP;
END;          

Received on Thu Feb 10 2000 - 10:26:45 CST