Procedure error. Please HELP...

From: <david_petit_at_yahoo.com>
Date: Fri, 29 Oct 1999 17:50:49 GMT
Message-ID: <7vcmpo$394$1@nnrp1.deja.com>

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 - 12:50:49 CDT