Functions

To read data in XML format from the TESTDBSOURCE schema, use the GET_ORDERS function:

CREATE OR REPLACE 
FUNCTION GET_ORDERS RETURN CLOB IS
retval CLOB;
BEGIN
   SELECT xmlelement("DataArea", 
                  xmlagg(xmlelement("MySalesOrder", 
                  xmlforest(ORDER_NUMBER as "OrderNumber", 
                     ORDER_STATUS as "OrderStatus", 
                     DATE_TIME as "DateTime", CUSTOMER as "Customer"))) 
                     as MySalesOrder)
                  .getClobVal()AS xml_frag
   INTO retval FROM TESTDBSOURCE.ORDERS;

RETURN retval;
END;

To write data in XML format in the TESTDBTARGET schema, use the F_PUT_ORDERS function:

create or replace
FUNCTION               F_PUT_ORDERS(xmlData VARCHAR2) RETURN NUMBER AS 
Order_NO varchar2(32767);
Order_Status varchar2(32767);
--NewOrderStatus varchar2(32767);
Customer varchar2(32767);
xmlContent  xmlType;
o xmlType;
c xmlType;
s xmlType;
xmlEData varchar2(32676);
BEGIN

xmlContent := xmlType(xmlData);
if(xmlContent is not null) then
  o:= xmlType.extract(xmlContent,'DataArea/MySalesOrder/ORDER_NUMBER/text()');
  Order_NO := o.getStringVal();
  Order_NO := utl_i18n.unescape_reference(Order_NO);
--read the values from xml  := 1;
  c := xmlType.extract(xmlContent, 'DataArea/MySalesOrder/ORDER_STATUS/text()');
  if(c is not null) then
  Order_Status := c.getStringVal();
  Order_Status := utl_i18n.unescape_reference(Order_Status);
  else
  Order_Status := 'test';
  end if;
  s := xmlType.extract(xmlContent, 'DataArea/MySalesOrder/CUSTOMER/text()');
  if(s is not null) then
  Customer := utl_i18n.unescape_reference(Customer);
  Customer := s.getStringVal();
  else
  Customer := 'test';
  end if;
end if;
--insert the values into database
INSERT INTO TESTDBTARGET.ORDERS(order_number,order_status,customer) values(Order_NO,Order_Status,Customer);
RETURN 1;
END F_PUT_ORDERS;

This procedure is also required to run the F_PUT_ORDERS function:

CREATE OR REPLACE PROCEDURE TESTDBTARGET.PUT_ORDERS
(xmlData IN VARCHAR2)
AS 
x NUMBER;
BEGIN
x := TESTDBTARGET.F_PUT_ORDERS(xmlData);
END PUT_ORDERS;