Re: Random numbers

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 14 Mar 2002 12:43:46 -0800
Message-ID: <a6r2220adt@drn.newsguy.com>

In article <3C90E6E2.9763296D_at_televar.com>, Bill says...
>
>Has anyone generated random numbers with Oracle. How? I'm attempting to
>generate test data using random numbers.
>

Here is a little procedure I use to do this

create or replace procedure gen_data( p_tname in varchar2, p_records in number ) authid current_user
as

    l_insert long;
    l_rows number default 0;
begin

    l_insert := 'insert into ' || p_tname ||

                ' select ';

    for x in ( select data_type, data_length, nvl(rpad( '9',data_precision,'9')/power(10,data_scale),9999999999) maxval

                 from user_tab_columns
                where table_name = upper(p_tname)
                order by column_id )
    loop
        if ( x.data_type in ('NUMBER', 'FLOAT' ))
        then
            l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
        elsif ( x.data_type = 'DATE' )
        then
            l_insert := l_insert ||
                  'sysdate+dbms_random.value+dbms_random.value(1,1000),';
        else
            l_insert := l_insert || 'dbms_random.string(''A'',' ||
                                       x.data_length || '),';
        end if;

    end loop;
    l_insert := rtrim(l_insert,',') ||

                  ' from all_objects where rownum <= :n';

    loop
        execute immediate l_insert using p_records - l_rows;
        l_rows := l_rows + sql%rowcount;
        exit when ( l_rows >= p_records );

    end loop;
end;
/

ops$tkyte_at_ORA9I.WORLD> create table t as select * from scott.emp where 1=0;

Table created.

ops$tkyte_at_ORA9I.WORLD> select * from t;

no rows selected

ops$tkyte_at_ORA9I.WORLD> exec gen_data('t',5);

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9I.WORLD> select * from t;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ---------- --------- ---------- ---------- ----------
 8595 qQNsjRdmGy UYvEJpJvJ        829 11-FEB-03    50242.5     431.47         70
 4388 LsYWYijqrx YdkNbpRan       2709 07-JUN-03   48496.96   11671.24         94
 1167 RjsanYkHnB VqpMWUhuD       7075 19-AUG-03   59842.78   98571.55         60
 7604 XJcJyqGRJX PcFMeFDQC       7636 19-APR-03   16145.09   32461.01         16
 1952 hcFxXQkQhj yuSeNEpbh       2364 02-AUG-02    2749.97    75103.9         29

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 

Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Thu Mar 14 2002 - 14:43:46 CST