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   
         BEGIN
               SELECT html_clients_t_seq.nextval
                INTO :new.client_id
                FROM  dual;
          END;
   END IF;
   END;


       Finally, you can auto generate numbers as serial numbers for each record before committing the record in PRE-INSERT Trigger e.g. CLIENTS  data block


SELECT NVL( MAX (client_id + 1),1) 
INTO    :clients.client_id
FROM  CLIENTS_T;

you can use any trigger that fits your requirements.

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


No comments :