Oracle Autonumber?

[CiberTHuG]

How do you create a field in an Oracle table so that it will generate its own key? I hope that makes sense.

[S@NSIS]

From Deja...

You can get a similar "autonumber" functionality programing an easy to code
"before insert trigger" that gets the next value of the sequence and put it
into the table for you.

You must use a sequence. To create a sequence see the "Create Sequence"
command of SQL Then on a trigger either on database (Before Insert) or
on a form (PRE-INSERT) you write the following Code:

Select Seq Name.NextVal
Into Field Name
From DUAL;

where "Seq Name" the name of the sequence you created and "Field Name"
the column which you want to be autonumbered.


Let supose that we have a table named A with a primary key column named id.
We also have a sequence called A_seq.

The following code builds the trigger who generates the primary key
automatically.

create or replace trigger TBI_A before insert on A

begin
--this generates de
select A_seq.nextval
into :new.id
from dual;
end;

[CiberTHuG]

Okay... your response didn't help. and I'm not sure i understand the formatting.

This is what I have....

A table named "Errors". It has a field named "ErrorNum". In Access and SQL Server, any time a new record is added to that row, that field will generate its own value, a UID. I am trying to port this application to use an Oracle DB, but the DDLs I was given have not helped.

I am trying to avoid changing the code to such a dramatic degree that I have two seperate products. How can I define this table to compensate for what the code is expecting?

[S@NSIS]

Oracle doesn't have an autonumber as such.

It has a 'Sequence' which can be used to figure out the next available UID before you insert a record.

Therefore you really only have two options.

1) Use the above. Get the next number in the sequence and use that.

2) Create a GUID to use.

Both of which will have to be put into a 'Before Insert' trigger.

S@NSIS

[CiberTHuG]

Okay, I found this article: http://www.jlcomp.demon.co.uk/faq/autonumb.html.

I think it is saying the same as you were (but with punctuation). If I understand, I can use this and it won't change the SQL statements already in the code, right?

If so, this will work. We never supply this value, so I'm not worried about that, but we will query and return it to the user.

[S@NSIS]

Your link doesn't work!

As for 'punctuation', if you look at the top of the first post I made, I said it came from Deja (I actually searched for it).

[CiberTHuG]

*rotfl* Sorry, the forums incorporated the sentence ending period into the URL.

It should be

http://www.jlcomp.demon.co.uk/faq/autonumb.html

not

http://www.jlcomp.demon.co.uk/faq/autonumb.html.

[S@NSIS]

I see

[CiberTHuG]

Whahooo... it works.