Navigation
Flickr recent photos
User login
Two ways to generate random values in Oracle
scott — Tue, 04/22/2008 - 14:37
Recently I was working on a project that involved scrubbing data from an oracle instance for a development environment. The data needed to remain intact so that foreign key constraints would be observed, but private information needed to be randomized and/or erased to protect users. I knew there was a function within oracle that allowed for the generation of random numbers, so I assumed there would be random character generation capability as well, so I set out on Google to find the function.
The very first search result however was a slightly different approach to the problem I was having. In this article the dba pointed out that it was possible to utilize functions from shared objects. This makes it possible to incorporate functions into things such as the body of stored procedures. In this case he was using the rand function within libc.so. I preferred to use a built-in function for this particular application, but it’s nice to know that in the future that that option is available.
CREATE OR REPLACE LIBRARY libc_l AS
'/lib/libc.so'; -- References C runtime library.CREATE OR REPLACE PACKAGE random_utl
AS
FUNCTION rand RETURN PLS_INTEGER;
PRAGMA RESTRICT_REFERENCES(rand,WNDS,RNDS,WNPS,RNPS);
PROCEDURE srand (seed IN PLS_INTEGER);
PRAGMA RESTRICT_REFERENCES(srand,WNDS,RNDS,WNPS,RNPS);
END random_utl;CREATE PACKAGE BODY random_utl
AS
FUNCTION rand RETURN PLS_INTEGER
IS
LANGUAGE C -- Language of routine.
NAME "rand" -- Function name in the
LIBRARY libc_l; -- The library created above.
PROCEDURE srand (seed IN PLS_INTEGER)
IS
LANGUAGE C
NAME "srand" -- Name is lowercase in this
LIBRARY libc_l
PARAMETERS (seed ub4); --Map to unsigned INT
END random_utl;(examples from: http://proquest.safaribooksonline.com/0596004729/sqlPR2-CHP-1-SECT-18)
I eventually found dbms_random.value for numbers and .string for characters. By default .value with return either 0 or 1 otherwise if you want to specify arguments it can take 2. The first being the lowest value, the second being the highest. String takes 2 arguments the first being a character, the second being the length of the string. Very simple if you can remember the name of the function or you have Google close at hand =)
SQL> select dbms_random.value(1,1000) num from dual;
NUM
_____________
876SQL> select dbms_random.string('A', 5) str from dual;
STR
__________________
^aIcB