Oracle - Alpha Numeric Sequence to maximize 2 digits

this is not possible with an oracle "sequence". you will have to create your own function to return the "next" value, but this is not giving you the same transactional "control" of what the oracle sequence object gives you (parallel access still distinct values)

Hi!

This could maybe help. Or at least give you a starting point. :)
Create a function around this sql

SELECT
    CHR(65 + (LEVEL-1)/1000)  || CHR(65 + (LEVEL-1)/1000)  || TRIM(TO_CHAR(MOD(LEVEL-1,1000),'00')) ALPAHA_NUM
FROM
    DUAL
CONNECT BY LEVEL <= 1000*26;

Select allOpen in new window

I agree with Guy.

Regards,
     Tomas Helgi

Guy - you're right and I understand.
That said, I'm looking for a way to pass a value say '01' to a function to get me the next value ('02') .
If '99' is passed, the next sequence would be 'A1'.  When 'A9' passed, 'B1'; when 'Z9' then 'AA', then 'AB' etc

Once this is figured out, how do I obtain the MAX value of this from the table to feed into the function within a Trigger?

The gist of the question is how to generate a 2 character "sequential" string that is triggered before insert.

Try this.

You can tweak it to just use numbers and letters but it will restrict you in the number of values you have.

I use between ascii 33 and 126.

The caveat is that it is an autonomous transaction so if the calling session rolls back, the 'key' values have been wasted.

Just call the function in your trigger.

drop table my_current_keys;
create table my_current_keys(first_char char(1), second_char char(1));
insert into my_current_keys values(chr(33),chr(33));
commit;

create or replace function generate_key return char
is
	PRAGMA AUTONOMOUS_TRANSACTION;

	my_first char(1);
	my_second char(1);
begin
	select first_char, second_char into my_first, my_second from my_current_keys for update;
	if ascii(my_first) = 126 and ascii(my_second) = 126 then
		raise_application_error(-20101, 'Out of key values.');
	end if;

	if ascii(my_second) = 126 then 
		my_first := chr(ascii(my_first)+1);
		my_second := chr(33);
	else
		my_second := chr(ascii(my_second)+1);
	end if;

	update my_current_keys set first_char = my_first, second_char = my_second;
	commit;

	return my_first || my_second;
end;
/

show errors

Select allOpen in new window

test it

declare
	mykey char(2);
begin
	for i in 1..10000 loop
		select generate_key into mykey from dual;
		dbms_output.put_line('Got: ' || mykey);
	end loop;
end;
/

Select allOpen in new window

The following creates a table, pks, snd populates it with all of the two-character alphanumeric combinations (assumes only uppercase alpha) plus an id indicating the sort order:

create table pks
(id number
,key varchar2(2));

create sequence pk_seq;

declare
v_char1 varchar2(1);
v_char2 varchar2(1);
v_pair  varchar2(2);
i number;
j number;
begin
-- get all numeric combinations
 for i in 48..57
 loop
  v_char1 := chr(i);
  for j in 48..57
  loop
    v_char2 := chr(j);
  v_pair := v_char1||v_char2;    
  dbms_output.put_line(v_pair);
  if v_pair > '00' then
  insert into pks values(pk_seq.nextval,v_pair);
  end if;
  end loop;
 end loop;

-- get all numeric/alpha combinations
 for i in 48..57
 loop
  v_char1 := chr(i);
  for j in 65..90
  loop
    v_char2 := chr(j);
  v_pair := v_char1||v_char2;    
  dbms_output.put_line(v_pair);
  insert into pks values(pk_seq.nextval,v_pair);
  end loop;
 end loop;

-- get all alpha/numeric combinations
 for i in 65..90
 loop
  v_char1 := chr(i);
  for j in 48..57
  loop
    v_char2 := chr(j);
  v_pair := v_char1||v_char2;    
  dbms_output.put_line(v_pair);
  insert into pks values(pk_seq.nextval,v_pair);
  end loop;
 end loop;

-- get all alpha combinations
 for i in 65..90
 loop
  v_char1 := chr(i);
  for j in 65..90
  loop
    v_char2 := chr(j);
  v_pair := v_char1||v_char2;    
  dbms_output.put_line(v_pair);
  insert into pks values(pk_seq.nextval,v_pair);
  end loop;
 end loop;
end;
/
Then creates a function passing in a key value to return the next value
create or replace function get_next_key(p_val in varchar2)
return varchar2 is
v_lastid number;
v_nextkey varchar2(2);
begin
select id into v_lastid from pks where key = p_val;
select key into v_nextkey from pks where id = v_lastid + 1;
return v_nextkey;
end;
/
Example:
SQL> select get_next_key('ZT') from dual;

GET_NEXT_KEY('ZT')
-------------------------------------------
ZU

This is quite funny: our apprentice had to "solve" exactly the same task ;-)
I'd ask for her solution so that I may post it here...

oh no - Guess I would'nt pass that apprentice task :)

>>passing in a key value to return the next value
and
>>Here is an easier solution:

Assumption:
You have to select 'p_val ' or 'Last_Key' from the table before you call this?

Possible issue:
Two transactions call it at the same time.  Could it not generate dupes?

.

. . I'm looking for a way to pass a value say '01' to a function to get me the next value ('02') .

The theory is that the application knows the current "key" and will call the function to generate a "next" key.
In a trigger, you would rather call some function to generate a sequence number to add to that key.

Perhaps I understood wrong, but the purpose is that from a known "key" you wish to get the next.
This would be outside the application bulk transaction  processing.

A simple example would be that certain "corporation" generates some document number starting at 'A' + sequence#, at the end of a set period (or whatever) they want to start with series 'B' - sequence 1, etc... (bum example for illustration purposes)

Therefore for transactional purposes, once you have the "key" you need a function to generate a sequence number.

GNOVAK pointed out the restriction of a normal sequence only able to generate 99 keys.  This meant to me the trigger would need to make a call similar to NEXTVAL to get the next key without knowing the current key.

I understand slightwv, but what is the use of a two character "key" if you don't use it as prefix to an alpha sequence?

The nextval and an alpha sequence is "useful" only if it actually generates an alpha sequence like prefix + number.

In fact some years ago I did code a package that generates alpha sequences similar to SAP, I need to look it up in my code archives.

I'm not disputing the asker's need for something like this (even if I really don't see the need).  I'm just questioning if you have the last value before you generate the next value.

If not, you would need to query some table to get the last generated value.  Where else does the 'last value' come from?

Using Oracle sequences as an example:  You can only get CURRVAL from a session that has previously called NEXTVAL.

You hit the spot. A two character "sequence" is useless, unless...
The real purpose is to combine it as an alfa prefix to some number.

And you are right. I myself saw no need for this two character alpha sequence alone, but in my thoughts I took it beyond as a basis to generate a true alpha sequence generated by concatenating the key with some number: oracle sequence or a value from a table.

The confusion may lie in the terminology, where we are using the oracle term "NEXTVAL" to indicate the next value in the sequence of that key.

This 'key' value should be controlled by the application which decides when it needs the next key (not nextval) or can use the current key.

There can be many ways on how this is implemented: a table? a config file? hybrid oracle sequences?.

When I do find my long ago (10+ years) implementation, I will not post it here but rather publish it.

PS; Another example would be that the identification of some legal documents in the Justice system begin with the year (maybe date?) as a prefix and include a sequence within that year (date).

Perhaps I took this simple request beyond the original requirement, but I believe our job is to think outside the "box" and perhaps our legacy is that many EE members will learn something from it.
:p

PS2: Besides the OP's requirements are "flawed", this is not true:

...if '99' is passed, the next sequence would be 'A1'

Next sequence actually should be: '9A' and NOT 'A1' (unless you know nothing about sequences).
:p

Thanks everyone - the table solution was the most straight forward and served the purpose. For educational purposes, I appreciate the one stop solutions as well. Thanks again

I know you've already accepted solutions but I got to thinking overnight that what you seem to be looking for is a trigger that would act like the normal trigger associated with a sequence to generate an "auto-incremented" field. As such, a function is really not needed. Create the pks table with the number/letter pairs that I showed earlier (i.e. all numbers first, followed by numbers then letters, followed by letters then numbers, followed by all letters). Then just create the trigger.

create trigger nextval_trg
before insert on yourtable
for each row
declare
v_lastkey varchar2(2);
v_id number;
v_returnkey varchar2(2);
begin
select max(key) into v_lastkey from yourtable;
select (id  + 1) into v_id from pks where key = v_lastkey;
select key into v_returnkey from pks where id = v_id;
:new.key := v_returnkey;
end;
/