If you’ve ever wanted to generate a random password, a random number, or just a random string, dbms_random is here to help. Here is an example of the different uses of this useful package:
BEGIN
/*
To initialize or reset the generator,
call the seed procedure. Seed with a
binary integer
*/
dbms_random.seed(1234535678);
/*
Get a random 38-digit precision
number, 0.0 <= value < 1.0
*/
dbms_output.put_line(
TO_CHAR(dbms_random.value)
);
/*
get a random real number between 1 and 5
*/
dbms_output.put_line(
TO_CHAR(dbms_random.VALUE(1,5))
);
/*
get a random integer between 1 and 100
*/
dbms_output.put_line(
TO_CHAR(TRUNC(dbms_random.VALUE(1,100)))
);
/*
get a random string.
First parameter:
'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters
Second Parameter: Length (max 60)
*/
dbms_output.put_line(
dbms_random.string('p',8)
);
/*
Seed with a string (up to length 2000)
*/
dbms_random.seed(
TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')
);
/*
get a random integer between 1 and 100
*/
dbms_output.put_line(
TO_CHAR(TRUNC(dbms_random.VALUE(1,100)))
);
END;
Possibly related:
- More on Generating Strings
- Introducing: Stumble Upon a Post
- Query returning individual chars
- links for 2007-02-09
- ORDER BY no order
Tagged dbms_random | Post a Comment
Hope everything is working out...
Speaking of random numbers, what do you think about this problem of mine:
http://thinkoracle.blogspot.com/2005/05/random-numbers.html
July 29th, 2005, at 8:47 am #Robert,
I could not understand your problem. However, here are some clarifications about the package
DBMS_RANDOMwhich I’m sure you already know, but does not hurt to add them here for future reference:The package
DBMS\_RANDOMis only available in Oracle version 8 and above.If you want to encrypt sensitive data, then you should use the built-in package
DBMS\_OBFUSCATION\_TOOLKIT, notDBMS\_RANDOM.For an analysis of
DBMS\_RANDOM, how it works and the quality of the generated random numbers or strings, I suggest this article.The following functions are not documented in Oracle 8i and Oracle 9i
July 29th, 2005, at 11:38 am #DBMS\_RANDOM’s documentation, but they are in Oracle 10g’s:NORMAL,VALUEandSTRING. A look at the source code ofDBMS\_RANDOMreveals these hidden and useful functions.Hi Eddie,
I can’t really figure out what’s the use for SEED procedure here. Can you explain further?
As I execute the code below, it give me the same result.
BEGIN dbms_random.seed(1234535678); dbms_output.put_line('Random1: '||dbms_random.value); END; Random1: .62004818671882084550610341425311827476November 21st, 2005, at 11:50 pm #edcas, the seed is needed to generate the random number or string. The seed can be a number or a string. If you do not initialize the generator by explicitly calling the seed procedure, a default seed is used, in that case, it is equal to:
TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') || USER || USERENV('SESSIONID')So, if you do not seed, and then call dbms_random.value, chances are you will not get the same random value.
The dbms_random package specification says: If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.
So, your observation is correct, given the same seed over and over again, you will get the same result each time you execute the same dbms_random procedure.
How can a seed be used? well, one example is when you want to generate a random password. You would seed the generator with the username, so distinct usernames will have distinct random passwords, but same usernames will have the same random password since the seed (username) is the same.
November 22nd, 2005, at 12:52 pm #