Randomize existing data with data generator?

[Brian Baker]

I have a table that contains several thousands of records. Is it possible to use the data generator to replace data in a few columns of this table, while leaving the rest of the data (ie, primary key) intact?

This would be things like FirstName, LastName, etc., which I see the Data Generator can already use. In my scenario I just want to UPDATE instead of INSERT.

 

[Marco Kalter]

The Data Generator can only generate Insert statements, so unless you can do something smart with a view and "instead of" triggers, this is not possible.

 

[Brian Baker]

Not being an Oracle pro, I'm not familiar with "instead of" triggers. Can you expound on your idea a little bit?

 

[Marco Kalter]

Sure. First you create a simple view. For example, a view on the DEPT table:

create or replace view v_randomize_dept as
  select * from dept

Next you create an instead of trigger on this view that performs an update of the DEPT in case of an insert:

create or replace trigger v_randomize_dept_insert
  instead of insert on v_randomize_dept
  for each row
begin
  update dept
     set dname = :new.dname,
         loc = :new.loc
   where deptno = :new.deptno;
end v_randomize_dept_insert;

Now, if you insert a record in the view, the existing record in the DEPT table will instead be updated:

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESRCH         DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

SQL> insert into v_randomize_dept
  2    (deptno, dname, loc)
  3  values
  4    (10, 'NEW_NAME', 'NEW_LOC');

1 row inserted

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 NEW_NAME       NEW_LOC
    20 RESRCH         DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

SQL>

If you use the data generator to insert random values in this view for existing records, then you will probably have accomplished what you wanted.

 

[Brian Baker]

Originally posted by Marco Kalter:
Sure.

Thanks Marco, I'll give this a shot.