create an counter_id

[markusin]

Hello ,
i am a newbie in Oracle
i want to know, how can i insert in my table, which on the construction doesn't have a counter_id, but just the attribute counter_id;
i want now to add in every row an id starting from 0.

I want to do it with Procedures and outsite with java.
can somebody help me please.
i do not know, how start such a procedure and how should i save my code.

thanks
markus

 

[markrem]

create sequence your_seq;

insert into your_table (id, ...) values (your_seq.nextval, ...);

it is more common to create a BEFORE INSERT trigger that gets the nextval from your sequence and inserts it into the ID column. this would also allow you to perform operations on the value, such as subtracting 1 to ensure your ID always starts with 0 per your request.

If you need to know the sequence number for other processing outside of INSERT statement, then use the RETURNING INTO clause of the INSERT statement to pass the number into any variable.

-Mark