Varchar sequence in Oracle

We know that oracle supports sequence, but it generates number type values.
So we can use oracle sequence for specifically only to numbers.

We use this sequence in triggers of a table to update the primary key value for the field of newly inserted record.
If the primary key field is number, it is ok to use the oracle sequence.
What to do if the primary key field is varchar? Also if requirement is to not store numbers in this primary key field.

In this case we can consider sequence as follows for varchar primary key fields.
A, B, C … Z, AA, AB, AC….AZ, BA, BC …BZ…ZA, ZB… ZZ, AAA, AAB…ZZZ, AAAA…..

To generate such kind of sequence, I would like to create an oracle function which internally uses an oracle sequence and retrieves number as usual.
But depending upon the number from sequence, it calculates the varchar sequence value based upon index.
Look at below table which is the input and output with respect to my varchar generate sequence oracle function

Input	Output
1	A
26	Z
52	AZ
125478	GCPB
677	ZA
18278	ZZZ
18279	AAAA

Step 1:
Create a sequence that starts from 1 increments by 1.
create sequence seq_varchar start with 1 increment by 1 nocache;
Step 2:

Run the below oracle function,
Input parameters:-
argSeqname – Name of the oracle sequence created.
argNextVal – Numeric value.
If 1 then to get next value from the oracle sequence.
Else to get current value from the oracle sequence
CREATE OR REPLACE FUNCTION Generatestr(argSeqName IN VARCHAR,argNextVal NUMBER)
RETURN VARCHAR
IS
tmpNum1 NUMBER;
tmpNum2 NUMBER;
tmpCode VARCHAR2(800);
argNumber NUMBER;
BEGIN
tmpCode:='';
tmpNum1:=argNumber;
IF(argNextVal=1) THEN
EXECUTE IMMEDIATE 'select '||argSeqName||'.nextval from dual ' INTO argNumber;
ELSE
EXECUTE IMMEDIATE 'select '||argSeqName||'.currval from dual ' INTO argNumber;
END IF;
//Calculting the varchar value from the varchar sequence based upon //index from oracle sequence
tmpNum1:=argNumber;
IF(argNumber>26) THEN
WHILE(tmpNum1>26)
LOOP
tmpNum2:=MOD(tmpNum1,26);
IF(tmpNum2=0) THEN
tmpNum2:=26;
END IF;
tmpNum1:=FLOOR(tmpNum1/26);
IF(tmpNum2=26) THEN
tmpNum1:=tmpNum1-1;
END IF;
tmpCode:=CHR(64+tmpNum2)||tmpCode;
END LOOP;
END IF;
tmpCode:=CHR(64+tmpNum1)||tmpCode;


RETURN tmpCode;

END;
/

Step 3:

Run the below sql query
SELECT Generatestr('seq_varchar',1) AS NextValue FROM dual
Which mean to get next value from varchar sequence

Output:-

Run again
SELECT Generatestr('seq_varchar',1) AS NextValue FROM dual
Which mean to get next value from varchar sequence
Output:-

Run
SELECT Generatestr('seq_varchar',0) AS NextValue FROM dual
Which mean to get current value from varchar sequence
Output:-


Conclusion:-

So the process of generating the next varchar values goes on as similar to number from oracle sequence.
This way of generating varchar sequence is using oracle sequence to get only index, but we need to calculate the varchar value based upon Index