Adding a sequence number to a table. what should be the datatype of that column? [message #18927] |
Thu, 21 February 2002 06:55 |
Sabrina
Messages: 76 Registered: February 2002
|
Member |
|
|
I have a table and i want to add a sequence to it.
My table
SQL> desc tbl_Claimtrack
Name Null? Type
----------------------------------------- -------- ----------------
HOSTKEY NOT NULL TIMESTAMP(4)
CLAIMID NOT NULL RAW(32)
FORMATTIME NOT NULL TIMESTAMP(4)
SEPARATIONTIME NOT NULL TIMESTAMP(4)
TRANSMITTIME NOT NULL TIMESTAMP(4)
BATCHSEQUENCE NUMBER(38)
HELDFLAG CHAR(1)
DELETEFLAG CHAR(1)
DEPOSITTIME NOT NULL TIMESTAMP(4)
Now, i want to add a column that is sequencenumber.. so how do it do it.. what is the datatype of that column?
How do i do it.. this is urgent thanks..
|
|
|
Re: Adding a sequence number to a table. what should be the datatype of that column? [message #18928 is a reply to message #18927] |
Thu, 21 February 2002 07:46 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
you can choose either number or varchar2.
if u choose number:
1)
SQL> create sequence seq1 start with 1;
2) create or replace trigger trig1
before insert on table1
for each row
begin
select seq1.nextval into new.seqcol from dual;
end;
/
if u choose varchar2:
1) same as above
2) create or replace trigger trig1
before insert on table1
for each row
begin
select to_char(seq1.nextval) into new.seqcol from dual;
end;
Note: replace seqcol with column u defined in table for sequence column
|
|
|