Re: PL/SQL script problem

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Thu, 9 Sep 1999 22:57:05 +0200
Message-ID: <936910681.20247.0.pluto.d4ee154e@news.demon.nl>

Hi James,
Try changing it in
UPDATE prdinfo p
set qty_sold=

     (select sum(order_qty)
      from order_in_detail
       where prod_id=p.prod_id)  -- correlated with main query!

where prod_id=v_tdrow.prod_id;
For some reason which is also unknown to me PL/SQL often has difficulty with queries which should have been correlated subqueries. If that still doesn't resolve it: this statement will create a row exclusive lock. When you issue a
select qty_sold
from prdinfo p
where p.prod_id = v_tdrow.prod_id
for update of qty_sold;
you will get a row share lock. Apparently -according to the message- you are running in some kind of deadlock situation on a remote table

Hth,

--
Sybrand Bakker, Oracle DBA
James Thomson <itabl7up_at_yahoo.com> wrote in message news:7r8cuo$bj0$1_at_nnrp1.deja.com...
> Can anyone find what is giving me this error:
>
> DECLARE
> *
> ERROR at line 1:
> ORA-02049: timeout: distributed transaction waiting for lock
> ORA-06512: at line 15
>
> Here is my script, if I substitute an actual product # for the 2
> v_tdrow.prod's and make the cursor only fetch one row, it works, so I
> think something is wrong with the way I'm doing the cursor or using it.
>
> DECLARE
> v_tdrow prdinfo.qty_sold%type;
> CURSOR info IS
> select distinct prod_id as prod_id from order_in_detail;
>
> BEGIN
>
> OPEN info;
> LOOP
> FETCH info INTO v_tdrow;
> EXIT WHEN info%NOTFOUND;
>
> UPDATE prdinfo set qty_sold=(select sum(order_qty) from
> order_in_detail where prod_id=v_tdrow.prod_id) where
> prod_id=v_tdrow.prod_id;
> END LOOP;
> close info;
> COMMIT WORK;
> END;
> .
> /
>
>
>
> ---------------------------
> James Thomson
> itabl7up_at_yahoo.com
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Sep 09 1999 - 15:57:05 CDT