Oracle PL/SQL for DBAs

Generating Positive Numbers

I need to generate a variety of account balances for our test data, all of which are numbers. My representative balance could be a whole number or a floating-point number with two places after the decimal, such as 12,345.98. For simplicity’s sake, let’s assume the bank does not allow overdrafts—that is, the balance cannot be negative. The DBMS_RANDOM package’s VALUE function returns a positive number greater or equal to zero and less than 1 with 38 spaces after the decimal point. Here is how I can use that function to get a number.

    CREATE OR REPLACE FUNCTION get_num
       RETURN NUMBER
    IS
       l_ret   NUMBER;
    BEGIN
       l_ret := DBMS_RANDOM.VALUE;
       RETURN l_ret;
    END;

When this function is called, it returns a number such as the following.

    SQL> COLUMN get_num FORMAT 0.99999999999999999999999999999999999999

    SQL> SELECT get_num FROM dual;

                                   GET_NUM
    --------------------------------------
    .4617512131361177187595598121854637555

Because the function has a purity level of WNDS (write no database state), we can also call this function directly in a SELECT statement or use it inside an INSERT.

For the moment, let’s take a detour into a different kind of application. Assume that I am building a scientific application where temperature readings from a thermometer have to be captured for later analysis. I can generate a list of numbers via the following code:

    BEGIN
       FOR ctr IN 1 .. 10
       LOOP
          DBMS_OUTPUT.put_line ('Temperature = ' || get_num || ' Celsius');
       END LOOP;
    END;

The output is:

 Temperature ...