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
