Re: difficult query (returning into) ???

On Fri, 9 Nov 2001 10:38:47 +0100, "Roy" <something_at_something.here> wrote:

>Hi,
>
>I am developing a webpage using an Oracle 8i database and OCI calls and I
>want to minimise the number of queries to Oracle.
>
>so....when I update a row in the database I want to use the 'returning into'
>clause but now comes the challenge.
>Question 1:
>situation
>user 1 retrieves a row (eg select field form table where
>uniquekey= 1 and status = 10)
>user 2 retrieves the same row (eg select field form table where uniquekey
>= 1 and status = 10)
>user 1 updates that row (eg update table set status = 20 where
>uniquekey=1 and status = 10 returning field, status into var1 , var2)
>user 2 updates that row (eg update table set status = 20 where
>uniquekey=1 and status = 10 returning field, status into var1, var2)
> this will fail because user
>1 already updated that same row
>
>In both update situations I want to give the user the new data (hence the
>returning clause)
>But because the second update returned '0 rows updated' the return fields
>are empty!!!!.... so in my programme I now select the new data with an extra
>select (remember I want to minimise the number of queries :-) )
>
>so now the question : how to force Oracle to fill the return values with the
>current row data if an update fails?
>
>
>Second question.....
>when I update a row with a returning into clause I only seem to be able to
>return fields from the table I update, but sometimes I want to join another
>table.
>
>eg:
>data-table has two columns (key, field and status)
>status-table has two columns (status and status_description)
>
>something like this
>update data-table
>set data-table.field='value'
>where data-table.key = uniquenumber
>returning data-table.field, data-table.status,
>status-table.status_description
>into :var1, :var2, :var3
>where status-table.status = data-table.status
>
>So the status.description comes from the 'status-table'. That doesn't seem
>to work so now I need to do an extra query in the status table...
>
>Nice mind-breakers for the weekend ?
>Thanks for any information,
>Roy
>
>
>
>
>
>
>
I think you should first study what a transaction is and what locking [Quoted] can do for you. Or you should search in the archives at groups.google.com the answer to a similar question outlining the difference between optimistic and pessimistic locking. These are not mind-breakers, these are attempts to drive directly to hell.
I also think you would be way better off by building your applications with Designer, doing so they will be 200 percent more robust than what you are doing now.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Nov 09 2001 - 13:12:42 CET