Forum Stats

  • 3,839,090 Users
  • 2,262,450 Discussions
  • 7,900,852 Comments

Discussions

Dynamic SQL assignment

676674
676674 Member Posts: 1
edited Dec 22, 2008 2:10AM in SQL & PL/SQL
Background:

I am writing changes history program for some important tables I need to keep a close notice at.

One table has 42 columns (EDT), the other 30. (ADT)
Basically for each update/insert/delete that happens to any one of those tables, a row with info will be added to a table i created (ALEX_CHANGES_TBL)

I wrote triggers (3 for each table, insert/delete/update) in PL/SQL that manages this, which at the moment looks like this: (only base structure)

DECLARE
NEW as new_row OLD as old_row
BEGIN
IF new_row.COLUMN1 <> old_row.COLUMN1 THEN
INSERT INTO ALEX_CHANGES_TBL
(TABLE_NAME, FIELD_NAME, PREVIOUS_VALUE, NEW_VALUE)
VALUES('EDT', 'column1', old_row.COLUMN1, new_row.COLUMN1);
END IF;

...

IF ***.COLUMN42 <> ***.COLUMN42 THEN
...
END IF;
END;

As you understand, this is tiring, uncomfortable, and plainly stupid to write such a code.

I need to find a way to make it dynamic, initializing an array or something once with the column names and then running in some loop and checking the changes.

Simply an array is not possible since I can't address to new_row/old_row by the column name (like in .NET using [ ])

I am almost sure I can't use EXECUTE IMMEDIATE since 'new_row' and 'old_row' is not defined in the SELECT context that will be used in the dynamic SQL statement.


Just wondering on a good way to write it.


Thank you,
Alex

Edited by: user10732002 on Dec 21, 2008 2:07 AM
Tagged:

Answers

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited Dec 21, 2008 11:30AM
    Alex,

    If you are using version 11g, then you can use flashback data archives for these journaling purposes. You can read about that here on my blog: http://rwijk.blogspot.com/2008/11/journaling-using-flashback-data.html

    If you are using an older version, you need to code it by hand and you'll want to use the builtin booleans updating, inserting and deleting. These tell you exactly what the operation was without having to check if any of the columns have changed. You can read about them here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1025109

    Regards,
    Rob.

    Edited by: Rob van Wijk on 21-dec-2008 17:29

    Added link
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055 is also worth to take a look at

    Regards

    Etbin
  • Gurjas
    Gurjas Member Posts: 1,190
    You can use FGA (Fine Grain Auditing) to get all the queries run on the particular table.
This discussion has been closed.