Oracle Sequence Tips

Oracle Sequence Tips

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,

     A sequence is a database object from which multiple users may generate unique integers. A sequence is a schema object that generates sequential numbers.

·        Sequence numbers can be used for one or for multiple tables in the same database schema.

·        You can also use sequences to automatically generate primary key values.

     Sequence Creation Prerequisites

1.     Users must have the CREATE SEQUENCE system privilege.

2.     To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege.

3.     The DBA is the one responsible for granting the users privileges.

   Sequence Syntax  

    CREATE SEQUENCE [SEQUENCE NAME]
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}]
    [{ORDER n | NOORDER}];

       A Single User Vs Multi-Users Sequence

    We all know about how to create a sequence Oracle Form side or how we create a database sequence then call it from Oracle Form.  that there are two methods for creating sequence numbers:

• Creating an auto-generated  Oracle Form Serial Number.
• Generating  auto-numbers using a database sequence.

      There is a significant difference between the two methods one is used for a single user application and the other is used for multi-user application.

       A Single User Application

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

SELECT NVL( MAX (acct_no + 1),1) 

INTO  :accounts.acct_no 

FROM  accounts;

Note: 

      The previous example will Never work for multi-session environment. Since we are running  the auto-generated serial number within one single session. This means if any user  committed changes to a database. No body can see the changes made by the other one.
    

         Multi-Users Application   

CREATE SEQUENCE SEQ_ACCT_NO

  START WITH 1

  MAXVALUE 99999999999

  MINVALUE 1

  NOCYCLE

  NOCACHE 

  NOORDER

/

A Sequence Form Call Syntax: 

        You can call the database sequence created from Oracle Form in    
  PRE-INSERT Trigger from ' ACCOUNTS'  data block 

BEGIN

  SELECT seq_acct_no.NEXTVAL

  INTO :accounts.acct_no

  FROM DUAL;

END;

   Common Errors To Avoid on Sequence Creation  

·      A cyclic sequence, the sequence must be capable to generate more values than the cache count specified. Otherwise, your sequence creation will be failed with the following error:

     ERROR at line 1: ORA-04013: number to CACHE must be less than one cycle.

·        A non-cyclic sequence, on the long run, will exceed its maximum value predefined on sequence creation. This will lead to raising the following error:

     ERROR at line 1:ORA-08004: sequence ACCT.NEXTVAL exceeds MAXVALUE and cannot be instantiated.

 

Note:

        Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.

Sequence Gaps Reasons

1.     Sequence can't retrieve the last number issued by NEXTVAL call before rolling back to a specific transaction. This is one of the sequence defects that leads to sequence gaps. In other words, if you roll back a transaction NO such syntax called  LASTVAL  exists in the sequence definition to start over the last sequence value before rolling back the transaction.

2.     Sequence Cache option on system failure also leads to sequence gaps.

3.     If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user.

    Learn more about:

·        Generate a Sequence With Numbers & Characters

·        Dependent Drop Down list

·          In-Direct Global Variable Access

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.