Delete Processing

In the case of delete, you can set the list of key columns. These columns will be put as part of the where clause of the delete. If the key column names are not supplied, then a new delete statement will be created for each ROW element of the XML document where the list of columns in the where clause of the delete will match those in the ROW element.

XSU Example 20: Deleting Operations per ROW (PL/SQL)

Consider the delete example shown below,

create or replace procedure testDelete(xmlDoc IN clob) is
  delCtx DBMS_XMLSave.ctxType;
  rows number;
begin

   delCtx  := DBMS_XMLSave.newContext('scott.emp');
   DBMS_XMLSave.setKeyColumn(delCtx,'EMPNO');

   rows := DBMS_XMLSave.deleteXML(delCtx,xmlDoc);
   DBMS_XMLSave.closeContext(delCtx);
end;
/

If we use the same XML document shown for the update example, we would end up with two delete statements,

DELETE FROM scott.emp WHERE empno=7369 and sal=1800 and deptno=30; 
DELETE FROM scott.emp WHERE empno=2200 and sal=2000 and hiredate=12/31/1992;

The delete statements were formed based on the tag names present in each ROW element in the XML document.

XSU Example 21: Deleting by Specifying the Key Values (PL/SQL)

If we instead want the delete to only use the key values as predicates, we can use the setKeyColNames function to set this.

create or replace package testDML AS
   saveCtx DBMS_XMLSave.ctxType := null;   -- a single static variable

   procedure insertXML(xmlDoc in clob);
   procedure updateXML(xmlDoc in clob);
   procedure deleteXML(xmlDoc in clob);

 end;
/

create or replace package body testDML AS
  
  rows number;
  
  procedure insertXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.insertXML(saveCtx,xmlDoc);
  end;
  
  procedure updateXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.updateXML(saveCtx,xmlDoc);
  end;
  
  procedure deleteXML(xmlDoc in clob) is
  begin
    rows := DBMS_XMLSave.deleteXML(saveCtx,xmlDoc);
  end;

begin
  saveCtx := DBMS_XMLSave.newContext('scott.emp'); -- create the context once..!
  DBMS_XMLSave.setKeyColumn(saveCtx, 'EMPNO');      -- set the key column name.
end;
/

Here a single delete statement of the form,

DELETE FROM scott.emp WHERE EMPNO=?

will be generated and used for all ROW elements in the document.

XSU Example 22: ReUsing the Context Handle (PL/SQL)

In all the three cases described above, insert, update and delete, the same context handle can be used to do more than one operation. i.e. one can perform more than one insert using the same context provided all of those inserts are going to the same table that was specified when creating the save context. The context can also be used to mix updates, deletes and inserts.

For example, the following code shows how one can use the same context and settings to insert, delete or update values depending on the user's input.

The example uses a package static variable to store the context so that the same context can be used for all the function calls.

create or replace package testDML AS
   saveCtx DBMS_XMLSave.ctxHandle := null;   -- a single static variable

   procedure insert(xmlDoc in clob);
   procedure update(xmlDoc in clob);
   procedure delete(xmlDoc in clob);

 end;
/

create or replace package body testDML AS
  
  procedure insert(xmlDoc in clob) is
  begin
    DBMS_XMLSave.insertXML(xmlDoc);
  end;
  
  procedure update(xmlDoc in clob) is
  begin
    DBMS_XMLSave.updateXML(xmlDoc);
  end;
  
  procedure delete(xmlDoc in clob) is
  begin
    DBMS_XMLSave.deleteXML(xmlDoc);
  end;

  begin
    saveCtx := DBMS_XMLSave.getCtx('scott.emp'); -- create the context once..!
    DBMS_XMLSave.setKeyColumnName('EMPNO');      -- set the key column name.
  end;
end;
/

In the above package, we create a context once for the whole package (thus the session) and then reuse the same context for performing inserts, udpates and deletes. Note that the key column ('EMPNO') would be used both for updates and deletes as a way of identifying the row.

The users of this package can now call any of the three routines to update the emp table.

testDML.delete(xmlclob);
testDML.update(xmlclob);

All of these calls would use the same context. This would improve the performance of these operations, particularly if these operations are performed frequently.