Re: calls to SQL

["Eric A. Eastman" <erice@ONTKO.COM>]

Hello Murray,

There might be a lock on that particular row of the table.  When
Oracle DML tries to lock a row that is already locked (like right before
it updates it) the default behavior is to wait until the other lock
goes away.  In some extreme cases that will never happen and your
program will just hang.

When did the command in SQL*plus, did you use the exact same values?

Since you are already selecting the same row, try adding "for update
nowait" right before the "end-select" that will bomb with a nasty error
if there is a lock on the row.  Then you can ask your DBA to remove the
stale locks.  I think there is also a nowait option on the update but I
haven't used it.

Hope this helps,
Eric


On Wed, 21 Jan 1998, Murray Leckie wrote:

> I was wondering if anybody has ever had any problems making an SQL
> update to an oracle database (version 7) with SQR (version 3).  I am
> getting my variables back in a display and the select is working okay
> however the program is hanging after the select at the call to SQL.  If
> I do the command through SQL without the variables, I have no problems.
>
> begin-procedure Update-ITM-VENDOR
> display $vend_id
> display $Inv_ITEM_ID
>
> begin-select
> a.vendor_id,
> a.inv_item_id
> from ps_itm_vendor a
> where a.inv_item_id = $inv_item_id
> end-select
>
> display 'working so far'
>
> begin-sql
> update PS_ITM_VENDOR
> set vendor_id = $vend_id
> where inv_item_id = $Inv_item_id
> end-sql
>
>
> R. Murray Leckie
> mleckie@sierrasys.com
> Telephone: (902) 425-6688
> Fax: (902) 425-6689
>

------------------------------------------------------------------------
Eric Eastman    | Ray Ontko & Co. | Adverbs are endangered; act quickly.
erice@ontko.com | Richmond, IN    | http://www.ontko.com/, ftp.ontko.com