Solved

How do I MERGE and then UNMERGE data

Posted on 2008-06-19
7
486 Views
Last Modified: 2013-12-19
We have a app using oracle db, it stores a master patient file with linked tables containing clinical data . Sometime the patient is entered twice so the user requires a function to merge the two patient clinical data to one client record - no problem
However some time the merge is done incorrectly so they wish to unmerge the data.
Does anyone know a function within Oracle which would enbale the database to rollback the data for only these 2 patients to a point before the merge

Thanks in anticipation
0
Comment
Question by:kevincox29
  • 2
  • 2
7 Comments
 
LVL 6

Accepted Solution

by:
dragos_craciun earned 250 total points
ID: 21823501
Use the flashback feature of Oracle.
Configure undo retention to a reasonable amount of time and then write something like:

SELECT * from TABLEX t AS OF TIMESTAMP TO_TIMESTAMP('2008-06-19 12:00:00', 'YYYY-MM-DD HH24:MI:SS')

this will show you the data in the table as it was at that time (if that data is still in the flashback space)
0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 250 total points
ID: 21823526
It isn't so much a "function within Oracle" that you need other than a routine to perform the de-merging which applies the required business rules.

If you are looking for a one-off fix and it hasn't been too long (hours) since the data was merged then you could look at using the flashback facility to take a copy of the data prior to the merge and use this to perform the de-merge.  If it has been many hours or days since the merge occurred you will have to write code to perform the de-merge as I said above.

An example of a flashback query:

SELECT * from table_name
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '50' minute)
0
 

Author Comment

by:kevincox29
ID: 21829294
Hi thanks for the answer.
With this query is this for all data to revert back, in my case I only want certain records to reert to their original state
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21829328
This is a select statement and therefore it is NOT changing any data.

You can use the statement to retrieve the data as it was BEFORE the data was merged.  If you only want to retrieve some rows from the table then add a WHERE clause (just like any other query).

Once you have identified the rows as they were BEFORE the data was merged you will need to de-merge the data.  How you do this depends on how the data was merged in the first place.  It may involve the following:

delete the new merged row from the patient table  (if a new row was created for the merged data)
insert the two old de-merged rows (retrieved by the flashback query) into the current patient table.

Alternativeyl, it may be simply a process of using the data retrieved from the flashback query to update the current records in the patient table.

I would suggest you use the flashback query to create a copy of the old table so that you can then use this copy table to update your current patient table.

CREATE TABLE OLD_table_name AS
SELECT * from table_name
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '50' minute)

Flashback queries aren't generally used as a means of coding business functionality.  That is, if your users/IT department requires a procedure by which they regularly need to de-merge data then I would suggest that using flashback queries isn't the way to go.  In this case you need to change your application design and build this new functionality.

If this is a one-off fix then by all means use the flashback query to help you, but I suspect by now the data will be too old to retrieve, unless your dba has set a long retention period.
0
 

Author Comment

by:kevincox29
ID: 21829535
So in conclusion we will need to write this into our application

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now