Debi Prasad

• Oct 6th, 2006

 

hi,01. alter table by adding one more column to the table as serial no.SQL > alter table (serial_no number);02.create a sequence SQL > create sequence test_seqstart with 1increment by 1nominvaluenomaxvaluenocachenocycle;then initialize the sequence SQL > select test_seq.nextval from dual;03. write a small cursor which will update the table something like this-- clip --set serveroutput ondeclare cursor numbering is select rowid from test; rowtrack rowid; beginopen numbering;loop fetch numbering into rowtrack; dbms_output.put_line(rowtrack); exit when numbering%notfound; update test set serial_no=test_seq.nextval where rowid=rowtrack; (test_seq is a sequence)end loop;end;/ -- clip --then run this script as sqlplus / @ test.sqlthis will update all the row in the column serial_no.04.Then next trasaction onward use seqence .

rajani

• Oct 10th, 2006

 

Since you want some column as an identifier for the whole row and sequence can start from 1, you can simply do following :

1. Alter table xyz_table add(id number);

2. Update xyz_table set id = rownum;

3. Get the max. id from the table xyz_table

4. create sequence with start value as the max. id+1

5. thats it... you can start using that sequence from then on.

I guess this is helpful.

prasadppai02

 Profile Answers by prasadppai02 

• Nov 23rd, 2006

 

ALTER TABLE tablename ADD SR_NO INT IDENTITY(1,1)

This will automatically add serial nos to existing columns & also to the

records which will be inserted in future 

sri

• Nov 24th, 2006

 

shell i use index?

rohan deshpande

• Jan 22nd, 2007

 

it is possible by using sequence..

create a such sequence that it's should be started from 1 or any number you want .then increment by our requirments.and set max value 2 lakh.and then by using sequence.NEXTVAL you can generate the numbe automatically...

Sandip Bhattacharjee

• May 12th, 2007

 

SQL> ALTER TABLE EMP ADD SR_NO INT IDENTITY(1,1)
2 ;
ALTER TABLE EMP ADD SR_NO INT IDENTITY(1,1)
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


The above commands is not working.
Pls specity what do you mean by identity(1,1)

utham

• Sep 4th, 2007

 

Identity is a SQL Server concept

The better way to do is use the alter command