oracle stored procedure to generate test data for any table

CREATE OR REPLACE PROCEDURE create_test_data(p_tname IN VARCHAR2
,p_records IN NUMBER)
authid current_user
AS
l_insert long;
l_rows NUMBER DEFAULT 0;
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE clone_’ || p_tname ||
‘ AS SELECT * FROM ‘ || p_tname ||
‘ WHERE 1=0’;

l_insert := ‘INSERT INTO clone_’ || p_tname || ‘ SELECT ‘;
FOR x IN (SELECT data_type, data_length
,rpad(‘9’, data_precision, ‘9’) / power(10, data_scale) maxval
FROM user_tab_columns
WHERE table_name = ‘CLONE_’ || 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;
/