- Published on 19 September 2013
I have an ongoing love relationship with the MERGE statement.
In 2003 It was was added to the ANSI-SQL standard, and in SQL serve 2008 this was added to Micrsosfts implementation of SQL, and has been available in Oracle since version 9i.
Still, not a lot of folks use it, mostly because they've never heard of it.
What MERGE does, is to combine conditional DML statements into one statement which uses a one-pass scan on the target table being accessed.
- Published on 02 September 2013
If you have an application that relies on configurational data in the database, chances are you want to track any changes made to that data, say in case something goes wrong in your application because of data changes.
Shadowing a configurational table is therefore a good idea, and this you can achieve by construcing a trigger which tracks any dml statements executed on a table, and logging each action.
- Published on 26 July 2013
Sometimes your application code needs to truncate tables automatically, for instance if you.are collecting some staging data for a small data warehouse or resetting some logging tables or something like that.
In those cases, you might sometime want to recycle the sequences used to populate data such as the id values, and in those cases you don't have a lot of options in Oracle.
To this end, I wrote the following procedures which take in a user name (schema name) and the table name, and locates any triggers on the table and from there it locates any sequences used by that trigger and then goes ahead and resets the sequence.
If you decide to use this, you must keep in mind that this recycles all sequences used by a table, so be careful.