The following is application development tip #5 from "30 tips in 30 minutes," brought to you by the IOUG. Return to the main page for more tips on this topic.
You may be called upon to move data as it changes from one database to another one. If you want, you can create an enormous amount of code to track changes in data. However Oracle replication technology can take care of that for you. Try this trick to move and format data from one database to another:
1. Create a materialized view log on the source table.
2. Create a database link on the target database for the source database.
3. Create a materialized view of the source table on the target database.
4. Create a stored procedure/package that processes a row from the materialized view table.
5. Create a trigger on the materialized view table to call the stored procedure/package.
During refreshes of the materialized view, the stored procedure will be called as the trigger fires. You can control the work performed by the stored procedure/package to place data in another table as you'd like it formatted or manipulated.
Note: do not change the data in the materialized view table.
For more information: See presentation, "Snapshot + Snapshot log + Trigger = Datamart," IOUG 2001.
Get more tips in minutes! Return to the main page.
About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.
IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.