How generate oracle string sequence?

Question

I need generate unique identifiers which must be no longer then 5 length, because of it numbers don't fit, it there any way generate unique strings in oracle database?

Answer 1

You may try to use Base 36: http://en.wikipedia.org/wiki/Base_36

SQL: Base 36 to Base 10 conversion using SQL only

Due to the comments to this answer, I'm adding an example:

This function converts base 10 number to the base which consists of letters.

create or replace 
function basen(n10 in number, letters in varchar2) return varchar2 as 
  s       varchar2(1000);
  len     number := length(letters);
  base10  number := n10;
begin
  if len <= 0 then
    return null;
  end if;

  loop
    if base10 < len then 
      return substr(letters, base10+1, 1) || s;
    end if;
    s := substr(letters, mod(base10, len)+1,1) || s;
    base10 := floor(base10 / len);
  end loop;

  return s;
end baseN;

This function converts from base10 to base36:

create or replace function base36(n10 in number) return varchar2 as 
begin
  return basen(n10, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ');
end base36;

You may create a common sequence with the appropriate MINVALUE and MAXVALUE and use one of the methods above in the trigger, SQL etc.

Answer 2

This method to use numbers in HEX format.

select to_char(seq.nextval, 'fm0XXXX')
  from dual;

Or this more compact method to use all symbols between ASCII code 33 and 122:

select num,
       chr(33 + mod(floor(num/(90*90*90*90)), 90))||
       chr(33 + mod(floor(num/(90*90*90)), 90))||
       chr(33 + mod(floor(num/(90*90)),90))||
       chr(33 + mod(floor(num/90),90))||
       chr(33 + mod(num,90)) as x00000
 from (select seq.nextval as num 
         from dual);

Basically this is a representation of 90-based number.

Answer 3

This might help: http://www.deep-data-mining.com/2012/06/five-ways-of-creating-unique-record.html

This bit looks useful:

According to Oracle document, SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes.

For example, on our Oracle system, I can do this:

select sys_guid() uniq_id from dual;

And get this:

059D828192804EABE05400144FFB6CA7

Next time I get:

059D828192A34EABE05400144FFB6CA7

Some of the numbers increment each time.

I could limit that to 5 characters, including the bits of the string which change each time it runs:

select substr(sys_guid(),9,5) uniq_id from dual;

To get e.g.

92D14