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

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *