Generate XML files and insert/update in another table using merge statement.

Hi Code seeker,

I am  explaining, generate XML file and insert into another table using Oracle merge statement.

Environment Oracle database11g, Oracle Linux 5.5, windows 7, TOAD.

Source table : emp
Destination table : emp_new

Step 1
create procedure for generate XML files.

create or replace procedure pro_emp( v_empno number) Is
 rc sys_refcursor;
BEGIN
 OPEN rc FOR SELECT * FROM emp where EMPNO=v_empno;
 dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , 'XML_FILES','clob2file.xml');
END;
/
---------------------------------------------------------
Step 2

exec pro_emp(7369)
create table emp_new as select * from emp;
---------------------------------------------------
Step 3

CREATE TABLE XML_TAB
(
  ID              NUMBER(10),
  FILENAME        VARCHAR2(100 BYTE),
  STATUS          VARCHAR2(20 BYTE),
  XML             SYS.XMLTYPE,
  PENDING_DATE    DATE,
  PROCESSED_DATE  DATE
)
ALTER TABLE xml_tab ADD ( CONSTRAINT xml_tab_pk PRIMARY KEY (id));
CREATE SEQUENCE xml_tab_seq;
-------------------------------------------------------------
Step 4

CREATE OR REPLACE PROCEDURE load_xml (p_dir       IN  VARCHAR2, p_filename  IN  VARCHAR2) AS
  l_bfile  BFILE := BFILENAME(p_dir, p_filename);
  l_clob   CLOB;
  l_dest_offset   INTEGER := 1;
  l_src_offset    INTEGER := 1;
  l_bfile_csid    NUMBER  := 0;
  l_lang_context  INTEGER := 0;
  l_warning       INTEGER := 0;
BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  -- loadfromfile deprecated.
  -- DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.loadclobfromfile (
    dest_lob      => l_clob,
    src_bfile     => l_bfile,
    amount        => DBMS_LOB.lobmaxsize,
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
  DBMS_LOB.fileclose(l_bfile);
  INSERT INTO xml_tab (id,filename,xml)
  VALUES (xml_tab_seq.NEXTVAL,p_filename,XMLTYPE.createXML(l_clob));
  COMMIT;
  DBMS_LOB.freetemporary (l_clob);
END;
------------------------------------------------------------
Step 5

EXEC load_xml(p_dir => 'XML_FILES', p_filename => 'clob2file.xml');
SET LONG 5000

SELECT * FROM xml_tab;

ID    FILENAME    STATUS    XML    PENDING_DATE    PROCESSED_DATE
445    clob2file.xml    PENDING    (ORAXML)    26/05/2019 0:30   
430    SOH_SESSION_20190415113503.xml    PROCESSED    (ORAXML)    15/04/2019 23:36    15/04/2019 23:36

Step 6

CREATE OR REPLACE PROCEDURE EMP_MERGE AS
BEGIN
  MERGE INTO EMP_NEW A
  USING
    (SELECT  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
  FROM XML_TAB M
     , XMLTABLE('//ROWSET/ROW' PASSING M.XML
       COLUMNS
             EMPNO      NUMBER(4)    PATH 'EMPNO'
           , ENAME      VARCHAR2(30) PATH 'ENAME'
           , JOB        VARCHAR2(9)  PATH 'JOB'
           , MGR        VARCHAR2(20) PATH 'MGR'
           , HIREDATE   VARCHAR2(20) PATH 'HIREDATE'
           , SAL        NUMBER(7)   PATH 'SAL'
           ,COMM        NUMBER(7)   PATH 'COMM'
           , DEPTNO     NUMBER(2)   PATH 'DEPTNO'
                 ) T
       WHERE STATUS = 'PENDING') B
  ON  (A.EMPNO = B.EMPNO)
  WHEN MATCHED THEN
  UPDATE SET
      A.ENAME            = B.ENAME,
      A.JOB              = B.JOB,
      A.MGR              =B.MGR,
      A.HIREDATE          =B.HIREDATE,
      A.SAL              = B.SAL,
      A.COMM               =B.COMM,
      A.DEPTNO           = B.DEPTNO
  WHEN NOT MATCHED THEN
  INSERT
     (EMPNO, ENAME, JOB,MGR, HIREDATE, SAL,COMM, DEPTNO)
  VALUES (B.EMPNO, B.ENAME, B.JOB,B.MGR, B.HIREDATE, B.SAL,B.COMM, B.DEPTNO);
   UPDATE XML_TAB
   SET STATUS = 'PROCESSED' WHERE STATUS = 'PENDING';
   END;

EXEC EMP_MERGE;

select * from emp_new;

 EMPNO    ENAME    JOB    MGR    HIREDATE    SAL    COMM    DEPTNO
7369    SMITH    CLERK    7902    17/12/1980    800    2345    20

SELECT * FROM xml_tab;
ID    FILENAME    STATUS    XML    PENDING_DATE    PROCESSED_DATE
445    clob2file.xml    PROCESSED    (ORAXML)    26/05/2019 0:30