Oracle PL/SQL for DBAs

Generating Negative Numbers

So far I have generated only positive numbers, not negative ones. But suppose that my banking application allows overdrafts—that is, borrowing money from the accounts temporarily. In this case, the account balance can be negative—a condition known as “being overdrawn.” In the test data, I may want to throw in some of those accounts, too. The DBMS_RANDOM.RANDOM function allows you to do this from within PL/SQL.

The RANDOM function

The DBMS_RANDOM package provides a function that can be used to generate random whole numbers, called RANDOM, which accepts no parameters and returns a binary integer in the range −2³¹ through 2³¹—that is, a whole number up to 10 digits long. The usage is straightforward:

    DECLARE
       l_ret   NUMBER;
    BEGIN
       l_ret := DBMS_RANDOM.random;
       DBMS_OUTPUT.put_line ('The number generated = ' || l_ret);
       l_ret := DBMS_RANDOM.random;
       DBMS_OUTPUT.put_line ('The number generated = ' || l_ret);
       l_ret := DBMS_RANDOM.random;
       DBMS_OUTPUT.put_line ('The number generated = ' || l_ret);
    END;
    /

The output is:

    The number generated = 865225855
    The number generated = 1019041205
    The number generated = -1410740185

Note how the numbers in this particular execution contain a mixture of negative and positive integers up to 10 digits long. There is no guarantee when calling this program that you will get a negative random number. If you need to be sure that your possibly negative random number conforms to specific formats, you will want to use a combination of both ...