Load data from XML or Excel file to oracle database with proper validation script
/
create table HR_temp_ERROR (ERROR varchar2(20), Status varchar2(20), col1 varchar2(20), col2 varchar2(20), col3 varchar2(20), col4 varchar2(20), col5 varchar2(20), col6 varchar2(20), col7 varchar2(20),col8 varchar2(20),col9 varchar2(20),col10 varchar2(20),col11 varchar2(20),col12 varchar2(20),col13 varchar2(20),col14 varchar2(20));
/
create or replace procedure proc_parseXml_new(pSessionId varchar2, pTransType varchar2, pOperation varchar2, pRawXMLdata clob) is
v_parser Dbms_Xmlparser.Parser;
v_Xml_Clob Clob;
v_Doc Dbms_Xmldom.Domdocument;
v_Child_Node dbms_xmldom.domnodelist;
v_xml_Node Dbms_Xmldom.Domnode;
v_empno number;
v_ename varchar2(20);
v_job varchar2(20);
v_hiredate varchar2(20);
v_errorMsg varchar2(20);
Begin
-- CLOB data
v_Xml_Clob :=pRawXMLdata ;
IF pOperation = 'RawUpload' THEN
-- Create XML Parser.
v_parser := Dbms_Xmlparser.Newparser;
-- Parse XML into DOM object
Dbms_Xmlparser.Parseclob(v_parser , v_Xml_Clob);
-- XML Document
v_Doc := Dbms_Xmlparser.Getdocument(v_parser );
-- Root element
v_Child_Node:=dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(v_doc),'/employees/employee');
--
For I in 0..dbms_xmldom.getlength(v_Child_Node)-1 LOOP
v_xml_Node := dbms_xmldom.item(v_Child_Node,I);
DBMS_XSLPROCESSOR.valueof(v_xml_Node,'empno/text()',v_empno);
DBMS_XSLPROCESSOR.valueof(v_xml_Node,'ename/text()',v_ename);
DBMS_XSLPROCESSOR.valueof(v_xml_Node,'job/text()',v_job);
DBMS_XSLPROCESSOR.valueof(v_xml_Node,'hiredate/text()',v_hiredate);
---Validation Script---
If v_job='NA' Then
v_errorMsg:= 'Job code should not be NA';
GOTO PrintError;
End if;
If v_hiredate is NULL Then
v_errorMsg:='Hiredate cannot be blank';
GOTO PrintError;
End if;
insert into HR_temp(col1, col2, col3, col4, col5,col6,col7)
values (v_empno,v_ename,v_job,v_hiredate,pSessionId, pTransType, pOperation);
<<PrintError>>
insert into HR_temp_ERROR(Error,Status, col1, col2, col3, col4, col5,col6,col7)
values (v_errorMsg,'Failed',v_empno,v_ename,v_job,v_hiredate,pSessionId, pTransType, pOperation);
End LOOP;
ELSE
dbms_output.put_line('in else block');
for I in
(select col1 EmpNo, col2 EName, col3 Job, col4 HireDate, col6 TransType from hr_temp
where pOperation='CommitUpload' and col5=pSessionId)
loop
dbms_output.put_line('in for loop');
If I.TransType='ADD' OR I.TransType='REMOVE AND ADD' THEN
dbms_output.put_line('in if block');
insert into emp(empno, ename, job, hiredate) values(I.EmpNo,I.EName,I.Job,I.HireDate);
END IF;
end loop;
END IF;
End;
/
--Call procedure proc_parseXml
declare
v_rawXMLData clob;
v_SessionId varchar2(20);
v_TransType varchar2(20);
v_Operation varchar2(20);
begin
v_TransType:='ADD';
v_SessionId:='qwer1234';
v_Operation:='RawUpload';
v_rawXMLData := '<employees> <employee> <empno>7369</empno><ename>SMITH</ename> <job>CLERK</job> <hiredate>17-DEC-1980</hiredate> </employee>
<employee><empno>7499</empno><ename>ALLEN</ename><job>SALESMAN</job><hiredate>20-FEB-1981</hiredate></employee></employees>';
proc_parseXml_new(v_SessionId, v_TransType, v_Operation , v_rawXMLData );
end;
/
declare
v_rawXMLData clob;
v_SessionId varchar2(20);
v_TransType varchar2(20);
v_Operation varchar2(20);
begin
v_TransType:='ADD';
v_SessionId:='qwer1234';
v_Operation:='CommitUpload';
v_rawXMLData := '<employees> <employee> <empno>7369</empno><ename>SMITH</ename> <job>CLERK</job> <hiredate>17-DEC-1980</hiredate> </employee>
<employee><empno>7499</empno><ename>ALLEN</ename><job>SALESMAN</job><hiredate>20-FEB-1981</hiredate></employee></employees>';
proc_parseXml_new(v_SessionId, v_TransType, v_Operation , v_rawXMLData );
end;
/
Comments
Post a Comment