Recommended way to test code updates in Oracle

Just from a logical standpoint you're going to have to have 2 copies of the data, or a log of each change (including the deletes/inserts). You can use the tables/triggers like you said, use flashback query (and that will work on 11g by the way, assuming that all the archive logs for the day are still online-- and, in my experience, it's not that expensive of a task depending on the query)

You can also approach it a little differently, although this method is more complex, it might be faster to some extent, especially if there's some limit to which rows are "in scope"-- meaning, that if you can put some kind of code to ignore the vast majority of the rows in the table (like a rule that they cannot update hours that are more than a month old or something similar).

You create a second table that has all the rows that are "in scope" for the update-- it's just a plain old copy of the other table as it sits right now.

When your job runs, it first deletes records from the table that are "out of scope" and then runs a query that gets the edited/deleted rows..

select t1.a as a1, t2.a as a2, t1.b as b1, t2.b as b2 
from t1
left join t2 on t2.id=t1.id
...

Start building your file with this info Be sure to UPDATE the t2 with the new values (and perhaps delete records that were deleted)

Then you run another that gets the inserted rows Add this info to your file as well Likewise, insert those rows into table 2.

In the end, it's a coinflip on whether or not this is faster-- but at least it'll run when you can schedule it-- but the flashback query is basically the same thing and letting oracle handle it.

It's much more simple and perhaps less error prone to just use flashback query in my opinion.

You can always nest blocks in PL/SQL

DECLARE
  <<define your other local variables>>

  PROCEDURE drop_if_exists( p_table_name IN VARCHAR2 )
  AS
    l_num_tables pls_integer;
  BEGIN
    SELECT COUNT(*)
      INTO l_num_tables
      FROM user_tables
     WHERE table_name = p_table_name;

    IF( l_num_tables = 1 )
    THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
    END IF;
  END drop_if_exists;
BEGIN
  drop_if_exists( 'MY_TEST_TABLE' );
  drop_if_exists( 'MY_TEST_TABLE' );

  <<more code here>>
END;