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.