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

·          Generate a Sequence With Numbers & Characters

·          Calling a secured Oracle Report 10g

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