This content has been marked as final. Show 5 replies
Unlike in SQL database identified data type, oracle does not have any such datatype. If you want to insert data using sequence then you need to user sequencename.nextval.
About *"If i truncate table"* . In this contest you would need to recreate the sequence because the nextval value in the sequence will not truncate.
You need to write a BEFORE INSERT trigger on the table to populate the id column with the sequnce..
To restart the sequence number, you need to manually do it once the table is truncated... Either drop and recreate or Follow this
always insert some data then want on row_id column which id start with 1.This is not a business case. A business case would be "Regulations demand a gap free sequence for invoice numbering". Even then this is an external requirement and does not determine a gap free sequence stored in the table. The ID of the table date might have gaps (created by a sequence), but when you query it you can include ROWNUMBER to generate a gap free numbering.
This way you don't have to care about resetting a sequence back to 1 when someone truncates the table.
If you really need a gap free sequence, then you can query MAX(id) + 1. Though you will have to serialize access (one at a time) to the table by locking it, otherwise 2 transactions might calculate identical numbers.
Edited by: Marwim on 20.02.2013 14:11