Add auto sequence value on insert a row in oracle Tuesday, April 28, 2015

Sequence:

CREATE SEQUENCE SEQ_ID
  START WITH 63
  MAXVALUE 9999999999999999
  MINVALUE 1
  CYCLE
  NOCACHE
  NOORDER;

Function:

CREATE OR REPLACE function BONDHON.func_get_seq_id return varchar2
is
v_id varchar2(50);
begin

select  To_char(sysdate,'ddmmyyhhmiss')|| SEQ_ID.nextval into v_id from dual;
return v_id;
end;
/

Tigger:

CREATE OR REPLACE TRIGGER TEST_SEQ_TRIGGER
BEFORE INSERT ON SCHEMA_NAME.TEST
FOR EACH ROW
BEGIN
 IF :new.ID IS NULL THEN
  SELECT SEQ_GET_ID.nextval INTO :new.ID FROM DUAL;
 END IF;
END;