2

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.

Mike Argyriou
  • 1,250
  • 2
  • 18
  • 30
George Pligoropoulos
  • 2,919
  • 3
  • 33
  • 65

1 Answers1

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.

Husqvik
  • 5,669
  • 1
  • 19
  • 29
  • Does this cover the case where you have column that belong to foreign keys? – George Pligoropoulos Dec 23 '15 at 14:47
  • Of course not. FK requirement wasn't in the description. But it's not a big problem to include it. Just query `ALL_CONSTRAINTS` and `ALL_CONS_COLUMNS` to find which columns are foreign keys and to which table they point to. And instead of totally random value pick random value from the target table. And of course you need to fill the tables in appropriate order. – Husqvik Dec 23 '15 at 16:42