Oracle: Delete and Re-Insert a Row in the Same Statement

It’s probably worth some explanation to understand where I want to drive you: when you run an update in Oracle, the changes are made at the point that is consistent during the whole execution of the update. This allows you to run a command like the one below, even if ID is the primary key of T:

update t
  set id=(case id 
             when 1 then 2 
             when 2 then 1 
             end)
  where id in (1,2);

If that was not the case, you would have to DEFER the constraint validation to the commit time every time you ran a command like the one above, or you would get an error like:

ORA-00001: unique constraint T_PK violated

To read more about how updates work in Oracle, look at Tom Kyte’s Write Consistency Part I, Part II, Seeing a Restart, and Part III, Why is a Restart Important to Us. But that’s a tangent to the subject of this post. What I’ll show here is that you can run a DELETE and an INSERT as parts of the same statement.

The test case

I’m not very sure what you can do with this hidden feature. I’m not sure either how Oracle supports it. What I do know is that it allows you to move a row without ENABLE ROW MOVEMENT, and without the need to DEFER constraints. To illustrate this point, you’ll find below a script that creates two tables, MASTER_T and DETAIL_T, that are linked by a foreign key with an ON DELETE CASCADE constraint:

create table master_t(
         id number,
         text varchar2(50),
         constraint master_t_pk 
                    primary key(id)
         );

insert into master_t 
       values (1,'Text 1');
insert into master_t 
       values (2,'Text 2');

create table detail_t(
         master_id number,
         constraint detail_master_fk 
                    foreign key(master_id)
                    references master_t(id)
                    on delete cascade);

insert into detail_t 
       values (1);

commit;

So the idea is to run the two following statements as if they were a single UPDATE:

delete from master_t 
       where id=1;
insert into master_t 
       values (1,'Text 3');
commit;

The tricks

As you might guess, there is not just one, but two tricks:

1. To avoid the referential constraint being checked before
   the INSERT is executed, I use a Streams Apply process and feed it the LCRs corresponding to the DELETE and INSERT above. To make Oracle apply the two statements as though they were executed at the same time, I use the same SCN and transaction identifier in the two LCRs.
2. If you use the LCR$_ROW_RECORD construct to create the LCR programatically, the SCN will be ignored. To workaround that issue, I define the LCRs with XML files, and I instantiate them with the DBMS_STREAMS.convert_xml_to_lcr function.

Note:
I’ve tested it with 11.1.0.7 on Linux x86 32 bits; it’s very likely
that the behavior of DBMS_STREAMS.convert_xml_to_lcr is not something wanted by Oracle.

The setup

To set up Streams, I have used the same schema for the tables, the streams queue, the apply process, and to instantiate the table. To make that setup easier, I have granted DBA to the schema owner. There is not much to pay attention to:

• Make sure the parameter apply_capture is
  set to false when you create the apply process so that it dequeues the persistent messages from the queue and not the buffered messages.
• If you don’t add any rule set to the apply (as I don’t), it will apply all the messages it gets from the queue.

Here is the script that creates the queue and the apply, and instantiates
the table:

begin
   dbms_streams_adm.set_up_queue(
       queue_table => 'custom_queue_table',
       queue_name  => 'custom_queue');
end;
/

declare
  v_name varchar2(256);
begin
   select value into v_name
     from v$parameter
    where name='db_unique_name';
 dbms_apply_adm.create_apply(
     queue_name => 'custom_queue',
     apply_name => 'custom_apply',
     apply_captured => false,
     source_database => v_name );
end;
/

declare
   v_scn number;
   v_name varchar2(256);
begin
   select value into v_name
     from v$parameter
    where name='db_unique_name';
   select dbms_flashback.get_system_change_number
          into v_scn
     from dual;
   dbms_apply_adm.set_table_instantiation_scn(
       source_object_name   => 'master_t',
       source_database_name => v_name,
       instantiation_scn    => v_scn);
end;
/
exec dbms_apply_adm.start_apply('CUSTOM_APPLY');

Enqueue the LCRs

Once everything is ready, you can build the LCRs and enqueue them in the queue so that the DELETE and the INSERT are applied to the table before the referential constraint is checked. Look at the ROWID to make sure the row really is deleted and inserted. You can also check from the table that it doesn’t impact the detail_t table (at least if the constraint doesn’t propagate the change to the detail table):

col id format 99
col text format a6 
select rowid, id, text
  from master_t;
ROWID		    ID TEXT
------------------ --- ------
AAATK5AAEAAAARuAAA   1 Text 1
AAATK5AAEAAAARuAAB   2 Text 2

declare
   v_name    varchar2(256);
   v_scn     number;
   x         varchar2(4000);
   y         varchar2(4000);
   xm        xmltype;
   v_any     anydata;
   enqopt    dbms_aq.enqueue_options_t;
   mprop     dbms_aq.message_properties_t;
   enq_msgid RAW(16);
begin
   -- Get DB Name and SCN
   select value into v_name
     from v$parameter
    where name='db_unique_name';
   select dbms_flashback.get_system_change_number
          into v_scn
     from dual;

   -- Define the Publisher 
   mprop.SENDER_ID := SYS.AQ$_AGENT(user, null, null);

   -- Build the DELETE LCR and enqueue it
x:='<ROW_LCR xmlns="https://xmlns.oracle.com/streams/schemas/lcr" 
xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="https://xmlns.oracle.com/streams/schemas/lcr 
https://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd">
  <source_database_name>[db]</source_database_name>
  <command_type>DELETE</command_type>
  <object_owner>[owner]</object_owner>
  <object_name>[name]</object_name>
  <transaction_id>[txid]</transaction_id>
  <scn>[scn]</scn>
  <old_values>
    <old_value>
      <column_name>ID</column_name>
      <data>
	<number>[id]</number>
      </data>
    </old_value>
    <old_value>
      <column_name>TEXT</column_name>
      <data>
	<varchar2>
[text][/text]
    '; x:=replace(x,'[db]',v_name); x:=replace(x,'[owner]',user); x:=replace(x,'[name]','MASTER_T'); x:=replace(x,'[txid]','1.1.111'); x:=replace(x,'[scn]',to_char(v_scn)); x:=replace(x,'[id]',1); x:=replace(x,'
[text][/text]
','Text 1'); xm:=xmltype.createXML(x); v_any:=dbms_streams.CONVERT_XML_TO_LCR(xm); DBMS_AQ.ENQUEUE( queue_name => 'custom_queue', enqueue_options => enqopt, message_properties => mprop, payload => v_any, msgid => enq_msgid); -- Build the INSERT LCR and enqueue it y:='<ROW_LCR xmlns="https://xmlns.oracle.com/streams/schemas/lcr" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://xmlns.oracle.com/streams/schemas/lcr https://xmlns.oracle.com/streams/schemas/lcr/streamslcr.xsd"> <source_database_name>[db]</source_database_name> <command_type>INSERT</command_type> <object_owner>[owner]</object_owner> <object_name>[name]</object_name> <transaction_id>[txid]</transaction_id> <scn>[scn]</scn> <new_values> <new_value> <column_name>ID</column_name> <data> <number>[id]</number> </data> </new_value> <new_value> <column_name>TEXT</column_name> <data> <varchar2>
[text][/text]
    '; y:=replace(y,'[db]',v_name); y:=replace(y,'[owner]',user); y:=replace(y,'[name]','MASTER_T'); y:=replace(y,'[txid]','1.1.111'); y:=replace(y,'[scn]',to_char(v_scn)); y:=replace(y,'[id]',1); y:=replace(y,'
[text][/text]
','Text 3'); xm:=xmltype.createXML(y); v_any:=dbms_streams.CONVERT_XML_TO_LCR(xm); DBMS_AQ.ENQUEUE( queue_name => 'custom_queue', enqueue_options => enqopt, message_properties => mprop, payload => v_any, msgid => enq_msgid); end; / commit; select rowid, id, text from master_t; ROWID ID TEXT ------------------ --- ------ AAATK5AAEAAAARuAAB 2 Text 2 AAATK5AAEAAAARvAAA 1 Text 3

Do it again

If you want to be able to replay the previous step, you can change the value of TEXT back to its previous value:

update master_t set text='Text 1' 
 where id=1;

commit;

And you will be able to run more tests.

Conclusion

To conclude this post, there are a few things worth mentioning. First of all, you can check that the detail table is not impacted by the DELETE and INSERT:

select master_id from detail_t;

MASTER_ID
---------
        1

Second, I must confess the “As parts of the same statement” is purely an invention of my criminal mind. What happens is that the referential constraint is checked after the insert. This only prevents an error on the foreign key. If you change the order of the two statements and run the INSERT before the DELETE, Streams will fail with:

ORA-00001: unique constraint (SCOTT.MASTER_T_PK) violated

My final remark is more, I think, a funny way to finish this post, provided you’re sure you’ve understood everything. I’ve imitated the Streams behavior with a MERGE command (i.e. kind of an UPDATE and an INSERT in the same statement). The result is very nice:

select rowid, id, text
  from master_t;

ROWID		    ID TEXT
------------------ --- ------
AAATLQAAEAAABY+AAA   1 Text 1
AAATLQAAEAAABY+AAB   2 Text 2

merge into master_t d
      using (select 'AAATLQAAEAAABY+AAA' rid from dual
             union all
             select 'AAATLQAAEAAABY+AAZ' from dual
             order by rid) s
   on (d.rowid=s.rid)
   when matched then
     update set d.id=3
   when not matched then
     insert (id,text) values (1,'Text 1');

ORA-02292: integrity constraint (SCOTT.DETAIL_MASTER_FK) violated - child

merge into master_t d
      using (select 'AAATLQAAEAAABY+AAA' rid from dual
             union all
             select 'AAATLQAAEAAABY+AAZ' from dual
             order by rid desc) s
   on (d.rowid=s.rid)
   when matched then
     update set d.id=3
   when not matched then
     insert (id,text) values (1,'Text 1');

2 rows merged.

select rowid, id, text
  from master_t;
ROWID		    ID TEXT
------------------ --- ------
AAATLQAAEAAABY+AAA   3 Text 1
AAATLQAAEAAABY+AAB   2 Text 2
AAATLQAAEAAABY+AAC   1 Text 1

commit;

select * from detail_t;

MASTER_ID
---------
        1