Re: SQL for NOT_LOCKED?

In article <94odja$l0m$1_at_nnrp1.deja.com>,   mikedba_at_my-deja.com wrote:
> We have an app in which the users will be editing many records, but
> they do not care to navigate to a particular record to edit. Instead,
> all they want to do is navigate to ANY other record that meets 2
> criteria: 1) It has a NULL in one particular field, and 2) it is not
> already being edited by somebody else.
>
> The app will be a Windows app (Delphi, using Direct Oracle Access),
 and
> we think we know how to handle everything except getting the query to
> bypass records that are locked by other users.
>
> If it is not practical to ascertain the locked status directly, we
 have
> considered many other approaches to accomplish the concurrency control
> that we will need.... but we'd be very happy to hear your suggestions
> if it sounds like we're going about this in a crazy manner.
>
> (Oracle 8.1.6 on Solaris)
>
> mIKE
>

I do not know Delphi, but in pl/sql I would open a cursor on all rows that meet the query criteria and then loop through the result set.

I would perform a select for update with the nowait option on each row from the driving cursor. If I get the row I update it and advance to the next row. On error (locked condition with immediate return from nowait) I just advance to the next row.

If coding like this in Delphi is not an option then perhaps you could write a database stored procedure (or package) and call the package from your application.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/