Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 −231 through 231—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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page