Home » SQL & PL/SQL » SQL & PL/SQL » Create UNQ Random Number
Create UNQ Random Number [message #310198] Mon, 31 March 2008 09:43 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
How to use oracle translate function to create a unique random number.

Thank you
Re: Create UNQ Random Number [message #310199 is a reply to message #310198] Mon, 31 March 2008 09:48 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You don't.

Either you want a random number, than use Dbms_Random, or you want a unique number than use a sequence.

Re: Create UNQ Random Number [message #310208 is a reply to message #310198] Mon, 31 March 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Are you sure you read and satisfy the 3 conditions requested in Not an EXPERT? Post in the NEWBIES forum, NOT here?

Read it, follow it.

What about dbms_random?

Regards
Michel
Re: Create UNQ Random Number [message #310310 is a reply to message #310208] Mon, 31 March 2008 20:57 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
As ThomasG said, a unique number is by definition NOT random. True randomness (even simulated randomness) will include the possibility of duplicates.

What is your REAL requirement that you are trying to support?

Do you want to avoid index contention with multiple inserts? If so, REVERSE key indexes may be the solution.

Ross Leishman
Re: Create UNQ Random Number [message #310391 is a reply to message #310198] Tue, 01 April 2008 01:36 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Oracle Supports GUIDs. These are Globally Unique identifiers.

I suspect this is what you are after.

SELECT SYS_GUID() FROM dual;


Also see this page:-

http://www.oracle-base.com/articles/9i/UUID9i.php
Re: Create UNQ Random Number [message #310394 is a reply to message #310391] Tue, 01 April 2008 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is not random.

Regards
Michel
Re: Create UNQ Random Number [message #310422 is a reply to message #310394] Tue, 01 April 2008 03:55 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
HATSTAND!

(That was pretty random Smile )
Re: Create UNQ Random Number [message #312225 is a reply to message #310199] Tue, 08 April 2008 04:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do this. You need a table with a single column, with a unique constraint on it.
Then your Get_Random function gets a value from dbms_random, and attemtps to insert it into this table. If the insert suceeds, you have a unique randomly generated number.
The performance of this will degrade slowly with the number of random records generated, but that's the price you'll have to pay.

Or, you could select random numbers of a size 7 or 8 orders of magnitude larger than the largest number you're likely to need - this won't guarantee uniqueness, but it will make it very very likely.
Re: Create UNQ Random Number [message #312747 is a reply to message #312225] Wed, 09 April 2008 12:57 Go to previous message
bella13
Messages: 90
Registered: July 2005
Member
thank you all
Previous Topic: How do I select columns from changing tables ?
Next Topic: Table doesn't exist?
Goto Forum:
  


Current Time: Sat Feb 08 17:17:47 CST 2025