Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Dynamic SQL assignment

676674
Member Posts: 1
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
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
Answers
-
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 -
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 -
You can use FGA (Fine Grain Auditing) to get all the queries run on the particular table.
This discussion has been closed.