I'm trying to perform this insert in a non-procedural environment:

INSERT INTO table
(column1,
column2)
SELECT
:col1value,
MAX(column2) + :count
FROM table
WHERE column1 = :col1value
;

My problem is that two or more processes might be executing this
statement concurrently. If so, they could get the same value for
MAX(column2). But I want the second process to get the updated value
for that aggregate that is set by the first process, and insert an
incremented value based on THAT.

Is there a way I can write this statement so one instance locks out
the second one? In my environment, one statement is all I'm allowed.

Thanks,
Elliott

shevine (AT) aol (DOT) com (Elliott) wrote in message news:<149413ab.0407061210.38cc8b7d (AT) posting (DOT) google.com>...

Use a sequence
replace the horrible expression by
<sequence>.nextval and you are set.

Sybrand Bakker
Senior Oracle DBA

Would that I could. Unfortunately, the business requirements are that
I know the generated values in advance of inserting them.

I've thought of establishing an intermediate table into which I could
insert the values from a sequence, together with other record
identifying information, to be used in a lookup later. But I'm dealing
with, in some cases, a couple of million rows per run, with a dozen
runs a day.

You can write me at eshevin (AT) ford (DOT) com if you'd care to hear more.

Elliott wrote:
<Various Random Ideas>
Have you thought about:

* Accessing a sequence once per run and adding 8 zeros to the end of the
number you get. Then add the number of the record to get a unique
identifer that is derived from a sequence. You could also use a sequence
that increments by 10 million each time. Oracle won't be phased by this
but check the size of the numeric data types used by non-oracle
languages to access the data.

* Using a numeric identifier that has decimal places. The left hand half
identifies the number of the row in the batch and the right hand half is
pulled from a sequence once at the start of the process.

* splitting the PK into two - a batch number (pulled from sequence like
above) and a number within a batch

</Various Random Ideas>

David Rolfe
Orinda Software
Dublin, Ireland