Link to home
Create AccountLog in
Avatar of new_perl_user
new_perl_user

asked on

How to change a column to Sequence number column in oracle

Hi,
 My table consists of following columns.

Sequence_number,
 Name,
ID....

Now I want to  the sequence number column to increment itself (1,2,...) when I insert a new row into the table

How can I do this.
ASKER CERTIFIED SOLUTION
Avatar of Om Prakash
Om Prakash
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of awking00
Does the table already contain data or is it new and empty?
If you already have data in the table and sequence number is null then you can update table

UPDATE table tbl_name
SET sequence_number = sequence_number_seq.nextval
where column_name = your_coumn_name
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
CREATE GLOBAL TEMPORARY TABLE DVZ_TEST1
(
  COLUMN1 NUMBER NOT NULL,
  DATE1 DATE DEFAULT SYSDATE NOT NULL,
  COLUMN2 VARCHAR2(4000)
, CONSTRAINT DVZ_TEST1_PK PRIMARY KEY
  (
    COLUMN1
  )
)
ON COMMIT PRESERVE ROWS
;

CREATE
TRIGGER DVZ_TEST1_TRG
 BEFORE INSERT ON DVZ_TEST1
FOR EACH ROW
BEGIN
  SELECT XXECSS_TRANSACTION_RECORD_S.NEXTVAL INTO :NEW.COLUMN1 FROM DUAL;
END;

;