Generating random numbers and strings in Oracle | Database Journal

Do you know how to car generate random numbers or strings in Oracle ? Generating random numbers is required when there is a need to create a lot of data for testing purposes, or when we simply need to use a number to temporarily tag a work. It may besides be necessity to generate random password strings of a fixate size–a very common necessity for websites that create and maintain logins for users .
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 /rdbms/admin directory.
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.

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

source : https://coinselected.com
Category : crypto topics