Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using a sequence to generate unique values

I've got an Oracle table wherein I need to create a column that would store a unique value in it as soon as a record gets created. I need this unique value/column for some integration purposes for data validataion. Would this be a sequence? If yes, how can I implement it for my licence_maint table?

Yes, you will want to use a sequence for generating your unique values. That is precisely what it is designed for. For starters, you'll need to create a sequence. This can be done similarly to the following:

CREATE SEQ my_seq START WITH 1 INCREMENET BY 1;
Next, to get the next value in the sequence, you need to select NEXTVAL for that sequence. Many people use the DUAL table for this purpose.
SELECT my_seq.NEXTVAL FROM DUAL;
Your application will want to take the value returned and use it to populate the column in your table. Or, you can also use a trigger to automatically populate this column whenever someone inserts a row into this table. Such a trigger may look like:
create trigger test_trig
before insert on test
for each row
declare
   seq_value NUMBER;
begin
   select my_seq.nextval INTO seq_value FROM dual;
   :new.columnX := seq_value;
end;
/

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close