Re: Will this work?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 04 Oct 1999 12:49:46 -0400
Message-ID: <H9n4N73pMbox2+bVD4s85d0bJ3r2@4ax.com>

On Mon, 4 Oct 1999 10:59:26 -0500, "Johnny Sloans Jr." <jsloans_at_cticallcenter.com> wrote:

You opened the cursor but never fetched from it.

An easier way to accomplish what you want might be

procedure my_proc(

  p_in_1 in number,
  p_in_2 in number,
  p_out_1 out number,
  p_out_2 out varchar2 ) is

begin
  for c in ( select o1, o2

               from t
              where i1 = p_in_1
                and i2 = p_in_2 )

  loop
    p_out_1 := c.o1;
    p_out_2 := c.o2;
  end loop;
end;

hope this helps.

chris.  

>Hi,
>
>
>
>I'm trying to get 8 values returned from an Oracle Stored procedure, but I'm not getting anything back. I'm passing in 4 values:
>
>B_date_in: 19990813
>
>E_date_in: 19990820
>
>sismas_in: 10681
>
>ani_in: 4198652657
>
>
>
>If I run this query in SQL worksheet I get:
>
>SQLWKS> SELECT nvl(sum(radur),0) sumDuration, nvl(sum(rarev),0) sumAmount, nvl(sum(rasich),0) sumPif, nvl(sum(comm_sbscrbr),0) sumSbscrbr,nvl(sum(comm_dstrb),0) sumCommDstrb, nvl(sum(decode(ratp6,1,1,0)),0) completed, count(ani) attempts, nvl(((sum(decode(ratp6,1,1,0))/count(ani))*100),0) percentage
>
>2> FROM admin1.el_commission
>
>3> WHERE to_char(call_date,'yyyymmdd') between 19990813 and 19990901
>
>4> and sismas = 10681
>
>5> and ani = 5023618182;
>
>SUMDURATIO SUMAMOUNT SUMPIF SUMSBSCRBR SUMCOMMDST COMPLETED ATTEMPTS PERCENTAGE
>
>---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
>
>17 28.2 3 13.34 0 6 66 9.09090909
>
>1 row selected.
>
>These are the output values I'm looking for from the following procedure.
>
>PROCEDURE el_sp_aadr2(B_date_in IN NUMBER,E_date_in IN NUMBER,sismas_in IN NUMBER, ani_in IN NUMBER, sumDuration_out OUT NUMBER, sumAmount_out OUT NUMBER, sumPif_out OUT NUMBER, sumSbscrbr_out OUT NUMBER, sumCommDstrb_out OUT NUMBER, completed_out OUT NUMBER, attempts_out OUT NUMBER, percentage_out OUT NUMBER) IS
>
>BEGIN DECLARE
>
>sumDuration NUMBER;
>
>sumAmount NUMBER;
>
>sumPif NUMBER;
>
>sumSbscrbr NUMBER;
>
>sumCommDstrb NUMBER;
>
>completed NUMBER;
>
>attempts NUMBER;
>
>percentage NUMBER;
>
>
>CURSOR record_cur IS
>
>SELECT nvl(sum(radur),0) sumDuration, nvl(sum(rarev),0) sumAmount, nvl(sum(rasich),0) sumPif, nvl(sum(comm_sbscrbr),0) sumSbscrbr,nvl(sum(comm_dstrb),0) sumCommDstrb, nvl(sum(decode(ratp6,1,1,0)),0) completed, count(ani) attempts, nvl(((sum(decode(ratp6,1,1,0))/count(ani))*100),0) percentage
>
>FROM admin1.el_commission
>
>WHERE to_char(call_date,'yyyymmdd') between B_date_in and E_date_in
>
>and sismas = sismas_in
>
>and ani = ani_in;
>
>BEGIN
>
>OPEN record_cur;
>
>sumDuration_out := sumDuration;
>
>sumAmount_out := sumAmount;
>
>sumPif_out := sumPif;
>
>sumSbscrbr_out := sumSbscrbr;
>
>sumCommDstrb_out := sumCommDstrb;
>
>completed_out := completed;
>
>attempts_out := attempts;
>
>percentage_out := percentage;
>
>CLOSE record_cur;
>
>END;
>
>END el_sp_aadr2;
>
>
>Does the syntax look correct, or am I missing something or is the logic incorrect? Any help would be appreciated, Thanks in advance.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Oct 04 1999 - 11:49:46 CDT