Press "Enter" to skip to content

How to Pass XML as 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 pass XML parameter to stored procedure to execute it. For this case below is the example PLSQL block showing that how you can pass XML as 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
v_XML   CLOB;
BEGIN

DBMS_LOB.CREATETEMPORARY (v_XML, TRUE);

P_XML_DUMMY (TO_CLOB (‘<main>
<DATA_RECORD>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>1981/02/20 00:00:00</HIREDATE>
<SAL>1600</SAL>
<COMM>160</COMM>
<DEPTNO>40</DEPTNO>
</DATA_RECORD>
</main>’));

END;

Pass xml as parameter to stored procedure in Oracle