Will this work?

From: Johnny Sloans Jr. <jsloans_at_cticallcenter.com>
Date: Mon, 4 Oct 1999 10:59:26 -0500
Message-ID: <e74K3.413$Pp1.190972@feed.centuryinter.net>

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. --

Johnny L. Sloans Jr.,
MIS, CenturyTel Inc.
jsloans_at_cticallcenter.com Received on Mon Oct 04 1999 - 10:59:26 CDT