PL/SQL - hiii have a table which consists of 2 lakh records and numbering is not there for these records.now

GeekInterview.com > Tech FAQs > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 49 of 155

hii
i have a table which consists of 2 lakh records and numbering is not there for these records.now i want to implement numbering for those records and also to the records which are going to be inserted in future..will it be possible with sequence.if so how???

Total Answers and Comments: 7

Last Update: September 04, 2007 Asked by: srikanth

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

October 06, 2006 01:49:36

Debi Prasad

RE: hiii have a table which consists of 2 lakh r...

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 .

Is this answer useful? Yes | No

October 10, 2006 16:51:21

rajani

RE: hiii have a table which consists of 2 lakh r...

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.

Is this answer useful? Yes | No

November 23, 2006 00:14:15

prasadppai02

Member Since: November 2006 Contribution: 2

RE: hiii have a table which consists of 2 lakh r...

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

Is this answer useful? Yes | No

November 24, 2006 00:10:35

sri

RE: hiii have a table which consists of 2 lakh r...

shell i use index?

Is this answer useful? Yes | No

January 22, 2007 05:14:53

rohan deshpande

RE: hiii have a table which consists of 2 lakh r...

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...

Is this answer useful? Yes | No

May 12, 2007 01:11:18

Sandip Bhattacharjee

RE: hiii have a table which consists of 2 lakh r...

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)

Is this answer useful? Yes | No

September 04, 2007 14:40:11

utham

RE: hiii have a table which consists of 2 lakh r...

Identity is aÂ SQL Server concept

The better way to do is use the alter command

Is this answer useful? Yes | No

Go To Top