alternative to auto increment with oracl

Anon

hi there,

I have to work with an oracle database. In principle everything is ok, but i am used to work with mysql and i use usually the auto incerement function for the table id. As far as I know oracle doesn't has this function. Is there any code/function out there that will mimic the auto increment inside my php script, so that I don't have to worry about my former scripts. All sql statements are simple with no mysql specific code in there, besides the auto increment in the table. All SQL statements in my scripts are independend, because I use a self made function to connect and work with the database, so I only have to change these functions to talk with oracle and this would allow to use the same old SQL statements. If there is a code that will mimic the auto increment, like searching the highest value and make the new entry plus one or something. It would save me some time if there is anything I can use.
Thanx in advance,

Andreas

jcleary

Like you said, I think the method of finding the largest value then adding one is your best guess

"select max(id)+1 from table"

The problem being that if two users created a record at the same time you might get two records with the same ID. So you could run a check on the table afterwards

select id, count(*) as countid group by id where countid > 1

If this returned a result you would then have to update one of them with a new ID.

I havent testes this, but you get the idea

John

[deleted]

Manuals are your friends...

Oracle supports the SEQUENCE type, effectively the same as auto_increment.

[deleted]

You can't do that without locking the table to make sure nobody else can write a new record, or even read the same result that you are looking for before you are finished inserting your record.
That means that you effectively make your database a single-user engine.

Anon

hi vincent,

sorry please tell me where I can find more about the SEQUENCE type. I am new to Oracle and haven't heard of it by now. If it is nearly the same as auto increment it would be wonderful

thanx

Andreas

Anon

Hi Andreas,

Vincent is right .. you should use a sequence. Oracle has many extra bits and bobs.

You can create a sequence in Oracle and tell oracle exactly what number you want the sequence to begin with.

CREATE SEQUENCE [schema.]sequence

       [INCREMENT BY integer] 

       [START WITH integer] 

       [MAXVALUE integer | NOMAXVALUE] 

       [MINVALUE integer | !!under!!NOMINVALUE] 

       [CYCLE | NOCYCLE] 

       [CACHE integer | NOCACHE] 

       [ORDER | !!under!!NOORDER]

Using it in PHP

$sel_seq_sql="SELECT Sequence_name.nextval FROM DUAL";

Just a quick explanation of the SQL above for you. The DUAL table is a dummy table in Oralce it contains rubbish and has just one column. You could right.

SELECT andreas from DUAL;

and it would return 'andreas'

The 'sequence_name.nextval' is thename you gave to your sequence and telling Oralce to take the next value in the sequence.

You will find some info on Oracle and PHP at this URL

http://php.net/manual/en/ref.oracle.php

Hope this helps!!

Maeve