Here is my scenario.
I have a simple table in Oracle 11g, into which rows are being placed. The primary key is a unique hex value, and there also an index on the MODIFIED date/time stamp.
My existing solution to returning a max number of records in a query is this:
SELECT
*
FROM
(
SELECT
rawtohex(utl_raw.cast_to_varchar2(id)) AS ID,
XMLCONTENT
FROM
<Database.tablename>
WHERE
STATUS = 'Some-Value'
and SECONDFIELD = 0
ORDER BY
MODIFIED
)
WHERE
ROWNUM <= MAX
I have new requirement, that concurrent clients running the same code can be used to increase throughput, while preventing duplicate reads from the table.
Using SQL (not PL/SQL) I would like to modify this query so that does these things:
Selects 2 fields from the table based on 2 WHERE conditions, ordering the rows by the MODIFIED value, as the above example does.
Then, in the same statement, Of the selected rows, selects up to MAX rows and locks them for UPDATE, skipping any rows which are already locked by another client instance.
Assigns a value to a field in all the locked rows
Commits the changes, unlocking the rows
Returns the rows which were modified to the caller.
I am looking for syntax suggestions on the best placement of the WHERE and ORDER clauses, and the best way to limit the locked and modified rows to the max, and where in the syntax the SET field='FOO', the new value, would go.
Regards
DS