Get Oracle PL/SQl variable property by name (reflection in PL/SQL)

Question

Hi, I need to do some auditing on updating a row.

So I have a function that receives a parameter of type some_table%ROWTYPE, containing the new values to be saved for that row.

I also need to save some info in a history table regarding what column values where changed. I was thinking of getting the column names for some_table from all_tab_columns, and then iterating over those to compare old and new values and see if it was changed. The problem is once I have the column name, I don't know how to access the value in my ROWTYPE variable. Something like var.getProperty(columnName).

I wanted to do it this way to avoid having a bunch of IFs, one for each field, and it would also work directly on adding a new column to the table.

Also I can't use triggers because the higher ups said "No triggers!". (If this is indeed the only way, I could try talking to them again about this).

Answer 1

It might be worth finding out why there is a 'no triggers' rule.

There's a lot of good arguments against triggers - especially about putting business rules in triggers - but logging is generally accepted as a good case for their usage.

It's also worth looking at Oracle's built in table versioning (which records a row per update) - this keeps the shape of the history in line with the current table shape. It doesn't give you the 'what changed' history, but it is probably better to do 'what changed' at the point when you are looking at the history, rather than adding the cost on every update.

The only way I've found of doing something like what you want - dynamically accessing a property of a %ROWTYPE, is to put a variable on a package header (so it is publicly visible) then doing dynamic PL/SQL. You could encapsulate the row variable, so long as your dynamic pl/sql block contained a local copy before each check. i.e. imagine this as your template for the execute immediate.

DECLARE
     lNew myTab%ROWTYPE;
     lOld myTab%ROWTYPE;
     lReturn PLS_INTEGER := 0;
BEGIN
    lNew := pStatefulPackage.NewRow;
    lOld := pStatefulPackage.OldRow;
    IF NVL(lNew.<variable>,'~') != NVL(lOld..<variable>,'~') THEN
        :lReturn := 1;
    END IF;
END;

Which is a lot of hassle to work around the fact you can't bind record variables or booleans in dynamic SQL.

It's also adding a lot of overhead on a per column basis.

Finally, I've found that ALL_TAB_COLUMNS is too slow to use for this sort of thing - you'd need to cache the metadata in local pl/sql memory.

Answer 2

It might be easiest to generate the body of the function, by reading the column names from USER_TAB_COLUMNS, e.g. (simplified, doesn't check for nulls):

select 'if :old.'||column_name||' <> :new.'||column_name||' then log('''||column_name||''',:old.'||column_name||',:new.'||column_name||'); end if;'
  from user_tab_columns
  where table_name='MYTABLE';

Answer 3

Take a look at Change Data Capture: http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/cdc.htm

But you really ought to just use triggers. I understand the desire to avoid triggers; programming can be very tricky when things silently happen in the background. But for situations like logging that's exactly what you want. Try calling it "aspect oriented programming" if you want to sound cool and impress the higher ups.