1

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?

3
  • How many values do you need to be able to represent? Using a normal sequence but storing the hex equivalent would take you from 100,000 values to 1,048,756, but that might still be too low? Using 26 characters would give 11,881,376 possible values; including digits would give 60,466,176. But more complicated that converting a sequence. Commented Oct 17, 2014 at 11:35
  • why the limitation on 5? (schema change would make more sense imo). Also, even if you stick with the 5 limitation, how many unique values do you require? Probably best to change the schema and use either a number or sys_guid. Commented Oct 17, 2014 at 13:26
  • The lemetation on 5 is because of documentation from client for ready rest api, I need more then ten billions objects. Commented Oct 17, 2014 at 17:01

3 Answers 3

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.

Sign up to request clarification or add additional context in comments.

Comments

2

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.

Comments

0

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

2 Comments

As I see you just cut the generated string and it can be not unique.
Yes, sorry, I think my answer was probably a load of old rubbish :-\ Sorry about that. I thought the number would be unique, but maybe it wouldn't be after all, I'm not sure. Sorry for wasting time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.