How to Pass XML as a Parameter to Stored Procedure in Oracle
Suppose you have a stored procedure in Oracle database which is using XML as parameter to process the data and you want to execute that stored procedure with XML parameter. For this case below is the example PLSQL block showing that how you can pass XML as a parameter to stored procedure in Oracle.
Follow exactly the steps of PLSQL block and change the XML data with your XML data and change the procedure p_xml_dummy with your stored procedure name. It can be function also method is same.
DECLARE<br /> v_XML CLOB;<br />BEGIN<br /><br /> DBMS_LOB.CREATETEMPORARY (v_XML, TRUE);<br /><br /> P_XML_DUMMY (TO_CLOB ('<main><br /> <DATA_RECORD><br /> <EMPNO>7499</EMPNO><br /> <ENAME>ALLEN</ENAME><br /> <JOB>SALESMAN</JOB><br /> <MGR>7698</MGR><br /> <HIREDATE>1981/02/20 00:00:00</HIREDATE><br /> <SAL>1600</SAL><br /> <COMM>160</COMM><br /> <DEPTNO>40</DEPTNO><br /> </DATA_RECORD><br /></main>'));<br /><br />END;