Oracle sql: Generate/INSERT Random data to a table for testing purposes

Oracle sql: Generate/INSERT Random data to a table for testing purposes

****************************** Code Begins **************************************************

-- ------------------------------------------------------------------------------------------
-- First of all, create the procedure given below
--
-- Secondly, call the newly create procedure from "sqlplus" as follows:
-- SQL> execute gen_data( 'EMP', 10000 );
--
-- where EMP is the table name and 10000 is the number of rows you want to insert
--
-- ------------------------------------------------------------------------------------------

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

dbms_application_info.set_client_info( 'gen_data ' || p_tname );
l_insert := 'insert /*+ append */ 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;
commit;
dbms_application_info.set_module
( l_rows || ' rows of ' || p_records, '' );
exit when ( l_rows >= p_records );
end loop;
end;
/

****************************** Code Ends **************************************************