PSOUG : 10/20/2004 : DBMS_RECTIFIER, Instead of Triggers, ORA_HASH

As a recent transplant to Seattle, I’m keen on getting connected with my local community of Linux, Open Source, BI, and Oracle brethren. I took in my first Puget Sound Oracle Users Group meeting last night. The group draws between 10-50 people per week, appears well organized, and based on my observations from my first meeting, provides substantial substance in a peer-to-peer setting.

Daniel Morgan, from the UW extension school, presented three mini-lectures on DBMS_RECTIFIER, Instead of Triggers, and ORA_HASH. I highly recommend you visit these pages on PSOUG, they have examples that demonstrate the following features quite nicely.

DBMS_RECTIFIER is a rather interesting package, especially for someone who spends a great deal of my Oracle time building CDC routines for Data Warehousing. At a basic level, it’s two procedures that provide a quick method to easily identify differences in data tables, and correct those changes. The rectify has an interesting behavior, and it’s not particularly well suited to CDC. However, the differences procedure quickly identifies differences (can be scoped to only a few columns as well) and places the different ROWIDs in a seperate table. In a pure Oracle environment, a BI professional could make use of this since ROWID provides a very quick way to access rows.

Instead of Triggers apparently have been availabe since Oracle 8. Some views are updateable, if they match a certain criteria. However, those that do not match this criteria, there is still a way to allow for updates/deletes/inserts on these views. Oracle allows you to make a special trigger, an Instead Of trigger that only applies to Views. They allow someone to specify exactly the DML to occur, and can include procedure and function calls, and DML on completely different tables. This could be used to :

  • Audit all inserts/updates through a defined view to a seperate audit table. I know this is old news, but this is a pretty slick way to accomplish this.
  • Provide an interface where certain DML statements become impossible. One can provide a view to a table that limits the DML so that certain columns are never updated.
  • Interface with an external system. One could create views in Oracle that provide an interface to a remote system. Data inserted/updated/deleted from these views could send these inserts/updates/delete to an external system via anything you can write in PL/SQL.

    ORA_HASH. Quick. Flexible. Hash Function. Useful for tons of things. If you know how to use HASHing, you’ll find this convenient. It too, has been in Oracle for ages. 🙂