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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.