Thank you.
Christopher Beck <clbeck_at_us.oracle.com> wrote in message
news:H9n4N73pMbox2+bVD4s85d0bJ3r2_at_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 - 13:18:10 CDT
