Martin,

You can create the unique index on the column.
Within your stored procedure, retrieve the max(<column_name>) and save
it in a variable. Increase the value by 1 (or what ever is the
increment). Use this value in the insert statement. With the unique
index on the column it will work very fast.

An alternative is to use a sequence:

Create sequence my_seq start with <some value, that goes after the
maximum value in the column>;

In the insert statement use nextval.my_seq as the new value for the
column.

This approach is not reliable, because sequence can be dropped or used
by another users/applications. In the first case you'll get an error in
the second case you'll get a valid number but it will be not what you
are expecting.
_________________________________________
Anna Lobova
Database Administrator
Tiburon, Inc.
[Email address protected]
Fremont office: (510) 792-2108 ext. 2040
Home office: (650) 348-1065
Cell: (510) 676-9699
www.tiburoninc.com


-----Original Message-----
From: Martin Jones
[mailto:oracledba-ezmlmshield-x6554247.[Email address protected]
Sent: Thursday, November 11, 2004 9:13 PM
To: LazyDBA Discussion
Subject: insert in a stored procedure

How can I use an insert statment in a stored procedure where I need to
get the next sequential number of one of the columns and add that to the
insert statement? Thank-you, Marty


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.




Oracle LazyDBA home page