Rollback after Merge?

[scottyjohn]

Hi all,
I have a situation where I have a table TABLE1 and a backup table BACKUPTABLE1 which is an exact copy of the main table, populated by running a query before a bulk merge to the TABLE1 table.
I am looking for the best way to handle some kind of rollback, and I have looked at rollback command, but I think MERGE carries an implicit commit which means there is no rollback available after it? I have thought of using TRUNCATE TABLE1 to delete the main data, and then sqlldr to move the data from BACKUPTABLE1 into TABLE1, thereby getting the table back to the prechange state. Trouble is that TABLE1 is queried constantly by another application for Voice Call Routing instructions, so it cant be empty at any point. Im struggling to come up with a solution, as Im quite new to this element of SQL.

IS there a solution to the above problem?

John

 

[LKBrwnDBA]

ROLLBACK should work fine!
.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[scottyjohn]

Hi I've tried the rollback statement and it doesn't work. The changes stay the same. I thought that when doing a merge, similar to an update or insert, a rollback is not possible as those commands are automaticallyfollowed by a sort of system commit which makes the rollback impossible?

John

 

[Thargy]

scotty,

what method are you using to access your oracle database? The reason I ask is that Java is infamous for having autocommit enabled by default, so everything always gets committed.

A rollback will definitely reverse a merge, it is DML, just like anything else.

I suggest that you copy the tables to duplicates, and delete all but a handful of rows. Then merge and rollback. With a small amount of data present, you should be able to inspect what was there before and after, and verify that the rollback has occurred.

Regards

T

 

[scottyjohn]

Hi,
I am calling the following sql from within a windows batch file using "sqlplus -s %CONSTR% @merge.sql" where the connection string is passed using a variable.

MERGE INTO schema1.routing r
USING schema1.batchupdate b
ON (r.dnis1=b.dnis1 and r.ivr_reason=b.ivr_reason)
WHEN MATCHED THEN UPDATE SET
      r.DNIS2=b.DNIS2,
      r.MENU_SCRIPT_1=b.MENU_SCRIPT_1,
      r.MENU_SCRIPT_2=b.MENU_SCRIPT_2,
      r.MENU_SCRIPT_3=b.MENU_SCRIPT_3,
      r.MENU_SCRIPT_TIMEOUT=b.MENU_SCRIPT_TIMEOUT,
      r.BUSINESS_GROUP=b.BUSINESS_GROUP,
      r.BRAND=b.BRAND,
      r.PRODUCT=b.PRODUCT,
      r.CAMPAIGN=b.CAMPAIGN,
      r.VIRTUAL_QUEUE=b.VIRTUAL_QUEUE,
      r.QUEUE_THRESHOLD=b.QUEUE_THRESHOLD,
      r.CALL_TYPE=b.CALL_TYPE,
      r.TREATMENT_1=b.TREATMENT_1,
      r.TREATMENT_2=b.TREATMENT_2,
      r.TREATMENT_3=b.TREATMENT_3,
      r.TREATMENT_TIMEOUT=b.TREATMENT_TIMEOUT,
      r.Q_MESSAGE_1=b.Q_MESSAGE_1,
      r.Q_MESSAGE_2=b.Q_MESSAGE_2,
      r.Q_MESSAGE_3=b.Q_MESSAGE_3,
      r.Q_MESSAGE_TIMEOUT=b.Q_MESSAGE_TIMEOUT,
      r.PRIORITY=b.PRIORITY,
      r.AGENT_GROUP=b.AGENT_GROUP,
      r.ACD_Q=b.ACD_Q,
      r.SKILL_1=b.SKILL_1,
      r.SKILL_2=b.SKILL_2,
      r.SKILL_3=b.SKILL_3,
      r.TIMEOUT_1=b.TIMEOUT_1,
      r.TIMEOUT_2=b.TIMEOUT_2,
      r.TIMEOUT_3=b.TIMEOUT_3,
      r.VQ_OVF_THRESHOLD=b.VQ_OVF_THRESHOLD,
      r.CALL_CLASSIFICATION=b.CALL_CLASSIFICATION,
      r.USE_BL_BUTTON=b.USE_BL_BUTTON,
      r.SWITCH=b.SWITCH,
      r.AGENT_EXCLUSIVE_CALL=b.AGENT_EXCLUSIVE_CALL,
      r.AGENT_LOGIN_ID=b.AGENT_LOGIN_ID,
      r.SEND_ALERT_ON_CALL=b.SEND_ALERT_ON_CALL,
      r.SEND_ALERT_ON_AUX=b.SEND_ALERT_ON_AUX,
      r.WAIT_STEP_TIMER=b.WAIT_STEP_TIMER,
      r.MULTI_BL_CALL_HANDLING=b.MULTI_BL_CALL_HANDLING,
      r.IVR_DIGIT_MAP=b.IVR_DIGIT_MAP,
      r.IVR_MAX_RETRY=b.IVR_MAX_RETRY,
      r.DEFAULT_LABEL=b.DEFAULT_LABEL,
      r.EXT_CC_DEST=b.EXT_CC_DEST ,
      r.VM_ENABLED=b.VM_ENABLED,
      r.VM_ROUTE_DN=b.VM_ROUTE_DN,
      r.VM_TIMER=b.VM_TIMER,
      r.TIMETABLE=b.TIMETABLE,
      r.SOFTPHONE_SOB=b.SOFTPHONE_SOB
WHEN NOT MATCHED THEN INSERT 
     (r.DNIS1,
      r.DNIS2,
      r.MENU_SCRIPT_1,
      r.MENU_SCRIPT_2,
      r.MENU_SCRIPT_3,
      r.MENU_SCRIPT_TIMEOUT,
      r.IVR_REASON,
      r.BUSINESS_GROUP,
      r.BRAND,
      r.PRODUCT,
      r.CAMPAIGN,
      r.VIRTUAL_QUEUE,
      r.QUEUE_THRESHOLD,
      r.CALL_TYPE,
      r.TREATMENT_1,
      r.TREATMENT_2,
      r.TREATMENT_3,
      r.TREATMENT_TIMEOUT,
      r.Q_MESSAGE_1,
      r.Q_MESSAGE_2,
      r.Q_MESSAGE_3,
      r.Q_MESSAGE_TIMEOUT,
      r.PRIORITY ,
      r.AGENT_GROUP,
      r.ACD_Q,
      r.SKILL_1,
      r.SKILL_2,
      r.SKILL_3,
      r.TIMEOUT_1,
      r.TIMEOUT_2,
      r.TIMEOUT_3,
      r.VQ_OVF_THRESHOLD,
      r.CALL_CLASSIFICATION,
      r.USE_BL_BUTTON,
      r.SWITCH,
      r.AGENT_EXCLUSIVE_CALL,
      r.AGENT_LOGIN_ID,
      r.SEND_ALERT_ON_CALL,
      r.SEND_ALERT_ON_AUX,
      r.WAIT_STEP_TIMER,
      r.MULTI_BL_CALL_HANDLING,
      r.IVR_DIGIT_MAP,
      r.IVR_MAX_RETRY,
      r.DEFAULT_LABEL,
      r.EXT_CC_DEST ,
      r.VM_ENABLED ,
      r.VM_ROUTE_DN ,
      r.VM_TIMER ,
      r.TIMETABLE,
      r.SOFTPHONE_SOB,
          r.OID)
VALUES 
     (b.DNIS1,
      b.DNIS2,
      b.MENU_SCRIPT_1,
      b.MENU_SCRIPT_2,
      b.MENU_SCRIPT_3,
      b.MENU_SCRIPT_TIMEOUT,
      b.IVR_REASON,
      b.BUSINESS_GROUP,
      b.BRAND,
      b.PRODUCT,
      b.CAMPAIGN,
      b.VIRTUAL_QUEUE,
      b.QUEUE_THRESHOLD,
      b.CALL_TYPE,
      b.TREATMENT_1,
      b.TREATMENT_2,
      b.TREATMENT_3,
      b.TREATMENT_TIMEOUT,
      b.Q_MESSAGE_1,
      b.Q_MESSAGE_2,
      b.Q_MESSAGE_3,
      b.Q_MESSAGE_TIMEOUT,
      b.PRIORITY ,
      b.AGENT_GROUP,
      b.ACD_Q,
      b.SKILL_1,
      b.SKILL_2,
      b.SKILL_3,
      b.TIMEOUT_1,
      b.TIMEOUT_2,
      b.TIMEOUT_3,
      b.VQ_OVF_THRESHOLD,
      b.CALL_CLASSIFICATION,
      b.USE_BL_BUTTON,
      b.SWITCH,
      b.AGENT_EXCLUSIVE_CALL,
      b.AGENT_LOGIN_ID,
      b.SEND_ALERT_ON_CALL,
      b.SEND_ALERT_ON_AUX,
      b.WAIT_STEP_TIMER,
      b.MULTI_BL_CALL_HANDLING,
      b.IVR_DIGIT_MAP,
      b.IVR_MAX_RETRY,
      b.DEFAULT_LABEL,
      b.EXT_CC_DEST ,
      b.VM_ENABLED ,
      b.VM_ROUTE_DN ,
      b.VM_TIMER ,
      b.TIMETABLE,
      b.SOFTPHONE_SOB,
      schema1.seq_routing.nextval
          );

exit;

So Im not sure if there is some implicit commit happening when this is executed, as Ive tested again and its definitely not happening

John

 

[Thargy]

scotty,

do you mean that it's not merging, or the rollback isn't happenin?. I thought you couldn't roll back, and that's why you were making the duplicate table to start with.

Regards

T

 

[scottyjohn]

Hi
It's the rollback that's not happening. After I run the above command, I run the rollback and it doesn't change the data.

John

 

[Thargy]

ok,

if you're using sqlplus and just running the above script, then I suggest you check your glogin.sql file, to make sure that some genius hasn't included the immortal
"SET AUTOCOMMIT ON" in it.

After that, from the precise session you're experiencing the problem with, run a create table statement for a dummy table, and then run an insert statement into that table.

Without committing, examine the contents of the table from a different session. If the different session can see your just-inserted data, then auto commit is happening.

If it is, then I would suspect either a logon trigger altering your sqlplus session, or one or more trigger(s) on the table in question. Check for the existence of these.

Regards

T