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.

Generate a Sequence With 

Numbers & Characters 

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,

    
  In fact, generating both numbers and characters are really a significant task in many business logic. Account numbers, sales' bills, traveling tickets, etc.

  

    Sequence  numbers are essentially required as a reference key  for each business 

   transaction. If a sequence number is generated, the sequence is incremented   independent of the transaction committing or rolling back.

Key Problem:

 On Trying to generate a sequence  number with a character string the same way as we with the sequence number do 

.'ACCT_00000000001' e.g. 

…

(START WITH TO_NUMBER('ACCT_00000000001',99,99,999

(MAXVALUE TO_NUMBER('ACCT_00000000001',99,99,99999

…

we will  suddenly sucked with such error

Result in the following:  ORA-01722: invalid number, as expected.

Key Reason:

Sequences always generate numbers

 it makes no sense to misuse the Sequence syntax rule to generate characters instead of numbers.

      

Key Solution 1:

               You can work around this issue...

You can simply use:

• Concatenation function
• Character function

     to concatenate that prefix or fixed character with the Oracle auto generated 
sequence.

Create Sequence  named SEQ_Acct_NO

Then, you can simply call the sequence

            

  SEQ_Acct_NO.NEXTVAL   

    Now let's think about generating  a character string...

    How can we attach a number to a character string...?

Concatenation function can accomplish this task.The 

     which direction can we attach the characters string ...?   

 "LPAD" functions or "RPAD" The 

    Choosing RPAD or LPAD  depends upon your application language direction.

*       If your application Language is 'English' or Left to Right then

 "LPAD" pads the left-side string with a specific set of characters.

*       If your application Language is Arabic or Right to Left  then 

 "RPAD" pads the right-side string with a specific set of characters.

 in PRE-INSERT Triggere.g. the ACCOUNTS block.

 BEGIN

  ('SELECT 'ACCT_'||LPAD(SEQ_ACCT_NO.NEXTVAL,10,'0 

   INTO :form_item_sequence 

; FROM dual 

;END

Key Solution 2:

Then in your insert statement use ACCOUNTS

INSERT INTO ACCOUNTS (..., acct_no) VALUES (..., 'ACCT_'||LPAD(seq_acct_no.nextval, 10,'0'));

    Learn more about:

        ·        Oracle sequence Tips                        

        ·        Import Oracle Database                        
      
         ·        Digital Clock Timer's Trick

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.

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.