Re: Procedure error. Please HELP...

David,

I can't see anything specific here that is wrong. What is the error that you are getting when you execute this procedure? Are you sure that t1, t2, and t3 are valid column names for the dummy_table table? I would need more infomation before being able to track this down.

Mark Prebilic
PrebSon Consulting

david_petit_at_yahoo.com wrote:

> I create the following procedure:
>
> create or replace procedure testing(a varchar2) as
> cursor c4 is select avg(decode(t1-abs(t1),0,t1,null)) t1,
> avg(decode(t2-abs(t2),0,t2,null)) t2,
> avg(decode(t3-abs(t3),0,t3,null)) t3 from
> dummy_table where dummy_id = a and dummy_code in ('A', 'B', 'C') group
> by dummy_id;
> rec4 c4%rowtype;
> t1_avg number(10);
> t2_avg number(10);
> t3_avg number(10,1);
> begin
> open c4;
> loop
> fetch c4 into rec4;
> exit when c4%notfound;
> if c4%found then
> exit;
> end if;
> end loop;
> close c4;
> t1_avg := round(rec4.t1, 0);
> t2_avg := round(rec4.t2, 0);
> t3_avg := round(rec4.t3, 1);
> end;
> /
>
> However, I find that when running this procedure, error occurs. (but the
> compilation is OK). Moreover, the SQL statment:
>
> select avg(decode(t1-abs(t1),0,t1,null)) t1,
> avg(decode(t2-abs(t2),0,t2,null)) t2,
> avg(decode(t3-abs(t3),0,t3,null)) t3 from dummy_table where
> dummy_id = a and dummy_code in ('A', 'B', 'C') group by a
>
> It is OK and return the correct result when running on SQL*Plus. Could
> anyone help me why I cannot run the above procedure? Which part of this
> procedure is wrong? In fact, I am not sure whether the cursor loop
> implementation is correct or not. This is becuase the cursor return one
> row result only with one dummy_id (i.e. a). However, I cannot use
> implicit cursor since it MAY returns nothing if dummy_id (i.e. a) can
> not be found. And I don't want to raise an exception.
>
> (avg(decode(t1-abs(t1),0,t1,null)) is used to average t1 with t1 value
> >= 0 only)
>
> Thanks,
> David
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Oct 29 1999 - 13:19:50 CDT