Generating random numbers and strings in Oracle | Database Journal
Whatever the indigence, the fact is that Oracle provides us with a random issue generator. This option is faster than writing your own random generation logic in PL/SQL as Oracle ’ s internal march logic is used. In accession, it can besides be used to generate both character and alphanumeric strings .
DBMS_RANDOM package
The DBMS_RANDOM software will generate random data in character, numeric or alphanumeric formats. The size and the image from which to pickup the random values can besides be specified. This box is created by the script dbmsrand.sql available in the
The comply functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM – generate random numbers.
VALUE – generate random numbers from the range provided. The range will be taken as 0-1 if none is provided .
bowed stringed instrument – render strings in amphetamine encase, lower case or alphanumeric format .
- The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
- U – Upper case
- L – Lower case
- A – Alphanumeric
- X – Alphanumeric with upper case alphabets.
- P – Printable characters only.
Providing any early character will return the output in upper shell only .
The size of the string should besides be provided as the moment parameter .
Oracle documentation says that it is necessity to initialize the package before using the random number generator. prophet by default initializes the box with the seed value as the stream exploiter diagnose, current time down to the irregular and the stream seance id .
INITIALIZE – Initialize the software to proceed with the number generation .
Provide a act ( seed ) as remark to the act .
SEED – Used to change the seed value. It is used in the inner algorithm to generate values. Setting this will
generate the random numbers in an order that will be alike in multiple sessions. Refer to the example below .
TERMINATE – Close the process of random issue generation.
Examples:
Below are some examples of using the software .
E.g. : Generating a random number ( positivist or minus )
SQL> select dbms_random.random from dual; RANDOM _____________ 1393936551
E.g. : Generating a random number between 0 and 1 .
SQL> select dbms_random.value from dual; VALUE _____________ 1
E.g. : Generating a random phone number from a image, between 1 to 1000 .
SQL> select dbms_random.value(1,1000) num from dual; NUM _____________ 611
E.g. : Generating a 12 digit random number .
SQL> select dbms_random.value(100000000000, 999999999999) num from dual; NUM _____________ 175055628780
E.g. : Generating an amphetamine case bowed stringed instrument of 20 characters
SQL> select dbms_random.string('U', 20) str from dual; STR _______________________ VUOQOSTLHCKIPIADIZTD
E.g. : Generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual; STR ____________________ xpoovuspmehvcptdtzcz
E.g. : Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in extra ( non-alphanumeric ) characters such as ‘ ] ’ in the string. This is resolved in Oracle 9i .
SQL> select dbms_random.string('A', 20) str from dual; STR __________________ sTjERojjL^OlTaIc]PLB
E.g. : Generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual; STR ________________________ SQ3E3B3NRBIP:GOGAKSC
E.g. : Generating a string of printable 20 characters. This will end product a string of all characters that could possibly be printed.
Read more: A Few Thoughts on Cryptographic Engineering
SQL> select dbms_random.string('P', 20) str from dual; STR ___________________ *Yw>IKzsjuI8K[IQPag
E.g. : exercise for calling the dbms_random software and setting the source for generating the same sic of random numbers in different sessions. Please note that the like random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first base issue generated was different in unlike sessions and the remaining were like. I recommend not using this choice in any of production code until it is by rights document by Oracle .
jaJA>declare 2 l_num number; 3 begin 4 l_num := dbms_random.random; 5 dbms_output.put_line(l_num); 6 dbms_random.seed('amar testing 67890'); 7 l_num := dbms_random.random; 8 dbms_output.put_line(l_num); 9 end; 10 / 483791552 478774329 PL/SQL procedure successfully completed.
Conclusion
DBMS_RANDOM is a full utility and will find its means into draw of growth projects, particularly web based ones. however, this package is not thoroughly documented. One should not use it just for the sake of it being there. Make certain that there is a true necessity or a necessity of random values before making use of this software. If you already have a custom code intend for the same determination, check out the benefits that are available when using this package compared to your application .
» See All Articles by Columnist Amar Kumar Padhi