difficult query (returning into) ???

From: Roy <something_at_something.here>
Date: Fri, 9 Nov 2001 10:38:47 +0100
Message-ID: <9sg870$677$1@news1.xs4all.nl>

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 Received on Fri Nov 09 2001 - 03:38:47 CST