Saturday, December 2, 2017

Handling Oracle Forms Sequence


In the name of Allah, Most Gracious, Most Merciful
Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that,


     You can smartly handle the Oracle forms sequence for your application with No Gaps as follow:


Task Definition:   


Increment a data block serial no by 1.


Task Solution:

To implement this task you need to follow these two steps:

1- Create a sequence for each table you need to increment it's sequence or primary key as follow:

       
        CREATE SEQUENCE   "HTL_CLIENTS_T_SEQ" 
      MINVALUE 1
      MAXVALUE  9999999999999999999999999999999999
      INCREMENT BY 1
      START WITH 1
      NOCACHE
      NOORDER
      NOCYCLE ;



2- Create a database trigger for each database table's serial or primary key to fire before insert for each row as follow:




CREATE or REPLACE TRIGGER CLIENTS_T
BEFORE INSERT ON CLIENTS_T FOR EACH ROW   
BEGIN     
  IF :NEW.CLIENT_ID IS NULL THEN   
       SELECT HTL_CLIENTS_T_SEQ.NEXTVAL
       INTO :NEW.client_id
       FROM  dual;
  END IF;
END;




Learn more about:

·             Oracle Sequence Tips


Hope this helps...


My success only comes from Allah, pls. note your comments and suggestions are great 
help for me in progress thanks in advance