Using Alpha Numeric Sequences in PL/SQL

Alpha Numeric Sequences are mostly used as unique keys in many business logics as it extends the range of the key without actually increasing its length. A typical alpha numeric sequence may start from 0000 to ZZZZ.


Sample Business Scenario

I have a table Sample_Seq (alpha_seq varchar2(4), blah_blah varchar2(100)).
The code intends to sequentially populate the alpha_seq field with alphanumeric numbers.

Eg :-

ALPHA_SEQ BLAH_BLAH
------------- ---------------
A999 AB
B000 CD
B001 EF
….

ABCD EF
ABCE EF

Given below is a code snippet that should accomplish the task.

DECLARE

alpha_seq varchar2(50) := ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’;

pos_a NUMBER:=0;
pos_b NUMBER:=0;
pos_c NUMBER:=0;
pos_d NUMBER:=0;

BEGIN

/* Get the latest sequence from the table and start incrementing
If no data found start from the beginning */
BEGIN
SELECT MAX(alpha_seq)
INTO max_seq
FROM Sample_Seq;

EXCEPTION
WHEN NO_DATA_FOUND THEN
max_seq := ‘ZZZZ’; -- So that the next sequence starts from A000
END;

/* Get the position of each characters in the string */

pos_a:= INSTR(alpha_seq,SUBSTR(max_seq,1,1));
pos_b:= INSTR(alpha_seq,SUBSTR(max_seq,2,1));
pos_c:= INSTR(alpha_seq,SUBSTR(max_seq,3,1));
pos_d:= INSTR(alpha_seq,SUBSTR(max_seq,4,1));


/* Get the next sequence */

pos_d:=pos_d+1;
IF pos_d> 36 THEN
pos_d:=0;
pos_c:=pos_c+1;
END IF;
IF pos_c> 36THEN
pos_c:=0;
pos_b:=pos_b+1;
END IF;
IF pos_b> 36 THEN
pos_b:=0;
pos_a:=pos_a+1;
END IF;

new_seq:=SUBSTR(alpha_seq,pos_a,1)SUBSTR(alpha_seq,pos_b,1)SUBSTR(alpha_seq,pos_c,1)SUBSTR(alpha_seq,pos_d,1) ;

INSERT INTO Sample_Seq (alpha_seq,blah_blah)
values(new_seq,’TEST’);

COMMIT;

END;

The logic used in the above code snippet is given below.

1) Declare and initialize a variable to hold the sequence of the alpha numeric key
In this case, our sequence starts from 0000 to ZZZZ and hence the sequence
alpha_seq is initialized in that order.

2) Use four variables (depends on the length of your key) to store the position of each character in the key in the alpha_seq variable.
Ie, if my key is ‘ABCD’, the position variables will hold the values 11,12,13 and 14.

3) The next step is to increment the position variables in a cyclic manner.
After one increment our new position keys would be 11, 12, 13 and 15.

4) Now get the characters in alpha_seq corresponding to these new positions.
This would give us the next sequence ABCE.

Using alphanumeric sequences as shown above is very economical in the sense that we can generate a large number of keys for a limited sized column.