Re: Insert Statement Issues

In article <9h7rpn$cf2h7$1_at_ID-68406.news.dfncis.de>, "James" says...
>
>I'm to add a record and am having problems
>
>Assuming my table (cat) has
>
>
>keyfield (number - primary), item_id (text - required)
>
>insert into cat (keyfield, item_id) values (keyfieldvalue,'2001.25b')
>
>the problem comes with the keyfielvalue, I need to increment the primary key
>by one. I tried the following
>
>insert into cat (keyfield, item_id) values (max(keyfield)+1,'2001.25b')
>insert into cat (keyfield, item_id) values ((select max(keyfield)+1 from
>cat),'2001.25b')
>
>ORA-00936: missing expression
>
>If I trade the max query for a straight out number it works fine
>
>insert into cat (keyfield, item_id) values (1457,'2001.25b')
>
>So how do I get the largest value in the table at present and then use it in
>my statement ?
>

Don't do that -- use a sequence! Otherwise you are limited to single user mode -- one user, one insert and everyone else waits.

If you believe you need "gap free" numbers, you should seriously consider your need for such a thing.

insert into cat ( keyfield, item_id ) values ( myseq.nextval, :x );

(use BIND variables as well)...

you could (but should not, unless you want to create the least scalable, most error prone application)

insert into cat (keyfield, item_id )
select max(keyfield)+1, :x from cat;

But then be prepared for tons of:

insert into cat select max(x)+1 from cat *
ERROR at line 1:
ORA-00001: unique constraint

as everyone who concurrently inserts will first be BLOCKED and then will FAIL with that error.

>
>
>--
>
>James Starritt
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp