How to populate/fill with random data an Oracle 11g table with Oracle SQL developer or other tool?

Question

We have several tables that we would like to test and in order to save time it would be very helpful to populate them with random data. We do not request the data to make sense. It can be any string or any date inside the row.

Because we are at a phase that the db schema changes frequently as we develop we do not want to create our own INSERT statements.

Instead we are looking for a tool that can do it automatically for any table.

Answer 1

Just as a start you can simply generate values using

CREATE OR REPLACE PROCEDURE fill_table(tableName IN VARCHAR2, owner IN VARCHAR2 DEFAULT USER, rowCount IN INTEGER DEFAULT 1)
IS
    commmandText clob;
    selectList clob;
    columnList clob;
    columnExpression VARCHAR2(255);
    separator VARCHAR2(2);
BEGIN
    IF rowCount <= 0 THEN RETURN; END IF;

    FOR columnData IN (SELECT column_name, data_type, data_precision precision, data_length, ROWNUM rn FROM all_tab_cols WHERE owner = fill_table.owner AND table_name = tableName AND column_id IS NOT NULL) LOOP
        columnExpression :=
            CASE columnData.data_type
                WHEN 'NUMBER' THEN 'dbms_random.value * ' || NVL(columnData.precision, POWER(10, 8))
                WHEN 'VARCHAR2' THEN 'dbms_random.string(''X'', ' || columnData.data_length || ')'
                WHEN 'DATE' THEN 'SYSDATE - dbms_random.value * 365'
                -- Add other types
            END;

        columnList := columnList || separator || '"' || columnData.column_name || '"';
        selectList := selectList || separator || columnExpression || CHR(10);
        separator := ', ';
    END LOOP;

    commmandText := 'INSERT INTO ' || owner || '.' || tableName || ' (' || columnList || ')' || CHR(10) || ' SELECT ' || selectList || 'FROM dual CONNECT BY LEVEL <= ' || rowCount;

    --dbms_output.put_line(commmandText);

    EXECUTE IMMEDIATE commmandText;
END;
/

EXEC fill_table('TEST_DATA', rowCount => 3)

You can turn the procedure into anonymous PL/SQL blocks if you don't have privileges to CREATE PROCEDURE.