and don't do something like
insert into (col1, col2) values ('1', '2');
to oracle. this is deadly!!!! if you do
insert into (col1, col2) values ('3', 4');
afterwards, oracle will not know this query.
it'll have to parse it again, because you used
literals. you have to use bindvars, if it is possible
with your oracle version. am only familiar
with 8i, so i can't tell. this query has to look:
insert into (col1, col2) values (:1, :2);
and you have to use ocibindbyname to bind
a phpvariable after ociparse. That way oracle
will parse your query only for the first time and
then take it out of the shared pool, if you come
along with another value. This is massively important!
Same for selcet or other queries. No matter what query, use
bindvars. You can read much more in the docs and e.g here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:528893984
337Michael "Michael Virnstein" <[EMAIL PROTECTED]> schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > This > > Read does not block read. > > read does not block write. > > write does not block read. > > write blocks write on the same column. > should read: > Oracle has a row locking mechanism, so > the following blocking mechanisms apply, when two > or more oracle sessions want to operate on the same row: > read does not block read. > read does not block write. > write does not block read. > write blocks write. > So if two ppl write on the same row on the same time, > oracle waits for the first transaction, to be committed, > or rollbacked, then for the second...and so forth. > > if you send your insert query with the select max(myrow)+1 form table; > and some other session inserts also with your statement at almost the > same time and commits in the meanwhile, that won't affect your max(myrow) > result in any way. oracle will bring you the result as it would have been > as you started your query.so it is for session b. the insert wont have to > wait, it > doesn't affect the same row as the other session. so you'll get the same > results. > > open two sql plus windows. > > in the first do: > > insert into acteursenc (nuacteur,nomacteur) > (select AA, BB from > (select max(nuacteur)+1 AA from acteursenc), > (select 'Michael Sweeney' BB from dual) > then in the second do: > > select max(nuacteur)+1 AA from acteursenc > then in the first > > commit > and in the second: > > select max(nuacteur)+1 AA from acteursenc > > yo'll see, that AA will be 1 higher the second time > > Michael > > "Michael Virnstein" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > the problem is, that the second sql produces more > > than one row. To be exactly the amount of > > select count(*) from acteursenc rows, with > > 'Michael Sweeney' as value in BB. Perhaps you > > have a unique key on nomacteur?! > > > > I could help further, if i know what you want to do > > with your query. > > if nuacteur is a pk, use a sequence!!!!! > > if two users send this query almost at the same time, > > you'll get two times the same pk. > > That's because of oracles locking mechanism: > > Read does not block read. > > read does not block write. > > write does not block read. > > write blocks write on the same column. > > if someone inserts a row with your statement, and hasn't commited his > > transaction, > > and someone else inserts a row with your statment before he has commited, > > then the two will get the same results for max(id)+1. A sequence will > never > > give the > > same result and is easy to use. and for your query, wouldn' this be > easier: > > insert into acteursenc > > (nuacteur, nomacteur) > > values > > (S_ACTEURSENC.NEXTVAL, 'Michael Sweeney') > > > > please explain what you want to do. > > > > Michael > > > > "Michael Sweeney" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > My following query : > > > > > > insert into acteursenc (nuacteur,nomacteur) > > > (select AA, BB from > > > (select max(nuacteur)+1 AA from acteursenc), > > > (select 'Michael Sweeney' BB from acteursenc))" > > > > > > produces an ORA-00001: unique constraint error. > > > > > > The primary key is nuacteur, but by setting AA to max(nuacteur)+1 I > should > > > be getting a new key that is unique, however it does not seem that way. > > > > > > What am I doing wrong here? > > > > > > > > > > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
