OraFAQ Forum: SQL & PL/SQL » ten-digit hexadecimal number

Home » SQL & PL/SQL » SQL & PL/SQL » ten-digit hexadecimal number (12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ten-digit hexadecimal number [message #683076]

Tue, 01 December 2020 01:31

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hello

I would like to ask you to give me an advice.

I need to generate unique ten-digit hexadecimal number.

It looks like this:

00037C534B
00037C55D0
00037C562B

Does anybody know how to do this the best ?

Thanks

Regards
Martin

Report message to a moderator

Re: ten-digit hexadecimal number [message #683078 is a reply to message #683076]

Tue, 01 December 2020 04:11

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select substr(SYS_GUID(),1,10) from dual connect  by level < 10;
SUBSTR(SYS
----------
3214508E95
3F1E5A3B47
4AB8043A78
AA1E91CB0D
69E781CA53
1F1886F54F
0231AF3F07
96CC564EC0
24D7DF2BAE

Report message to a moderator

Re: ten-digit hexadecimal number [message #683079 is a reply to message #683078]

Tue, 01 December 2020 04:13

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select to_char(dbms_random.value(0,1099511627775),'XXXXXXXXXX') from dual connect  by level < 10;
TO_CHAR(DBM
-----------
 85C6F6BA47
 C56CD89CE7
 5F5C0834E2
 D647A47AF7
 9DE6FCA93B
 B17D150B82
 6D8C29CDFA
 C399A2D124
 7A7BE656D6

Report message to a moderator

Re: ten-digit hexadecimal number [message #683082 is a reply to message #683079]

Tue, 01 December 2020 06:08

Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel,

Random != unique (also you need FM modifier otherwise TO_CHAR will produce 11 characters - leading space for plus sign). Chance of duplicate GUID is exteremely low and even though substr 10 out of 16 hex digits from GUID makes that chance a bit higher it is still extermely low but it exists. OP can simply use sequence to get 100% guaranteed uniqueness:

SQL> create sequence seq;

Sequence created.

SQL> select  to_char(seq.nextval,'FM000000000X') hex10
  2    from  dual
  3    connect by level <= 32
  4  /

HEX10
-----------
0000000021
0000000022
0000000023
0000000024
0000000025
0000000026
0000000027
0000000028
0000000029
000000002A
000000002B
000000002C
000000002D
000000002E
000000002F
0000000030
0000000031
0000000032
0000000033
0000000034
0000000035
0000000036
0000000037
0000000038
0000000039
000000003A
000000003B
000000003C
000000003D
000000003E
000000003F
0000000040

32 rows selected.

SQL>

[Updated on: Tue, 01 December 2020 06:12]

Report message to a moderator