How to pass xml as a parameter in Oracle?

Anil Jha

I have a datatable in C# that I want to pass as parameter in oracle stored procedure by converting into xml. I have achieved it using sp_xml_preparedocument in SQL Server but, I want to implement same in oracle.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sundaram Subramanian9y ago
Hoep this may help,

OracleCommand DbUpdateCommand = null;
OracleLob tempLOB = null;
DbUpdateCommand.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
DbUpdateCommand.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output;
DbUpdateCommand.ExecuteNonQuery();
//Assign the value to the LOB
tempLOB = (OracleLob)DbUpdateCommand.Parameters[0].Value;
tempLOB.BeginBatch(OracleLobOpenMode.ReadWrite);
//Convert the string to byte array to write to LOB
UnicodeEncoding encoding = new UnicodeEncoding();
byte[] renewalDetailXMLBytes = encoding.GetBytes(renewalDetailXML);
tempLOB.Write(renewalDetailXMLBytes, 0, renewalDetailXMLBytes.Length);
tempLOB.EndBatch();
DbUpdateCommand.CommandText = "P_WEB_PRDCR_RNEW_UPDT";
DbUpdateCommand.CommandType = System.Data.CommandType.StoredProcedure;
//Missing line - start
DbUpdateCommand.Parameters.Clear();
//Missing line - end
DbUpdateCommand.Parameters.Add("PN_KEY_AGNT_RNEW_HDR",
System.Data.OracleClient.OracleType.Number, 12).Value =
agentRenewalHeader;
DbUpdateCommand.Parameters.Add("PN_KEY_CO",
System.Data.OracleClient.OracleType.Number, 12).Value =
companyCode;
DbUpdateCommand.Parameters.Add("PC_RNWL_DETL_XML",
System.Data.OracleClient.OracleType.Clob).Value =
tempLOB;
DbUpdateCommand.Parameters.Add("PS_USR_NM",
System.Data.OracleClient.OracleType.VarChar,255).Value =
userName;
DbUpdateCommand.ExecuteNonQuery();
Gokhul Varman9y ago
If you refer the link you can get a clear solution about it:https://www.foxinfotech.in/2017/03/how-to-pass-xml-as-a-parameter-to-stored-procedure-in-oracle.html
Hariharan Krishnamoorthi9y ago
Hi Anil,
In Oracle you need to use XMLType datatype.
Please have a look at the below URL.
http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb04cre.htm#i1024833
Anil Jha9y ago
Vignesh,

The link explains about xml using SQL Server but I am looking for Oracle implementation.
Vignesh Mani9y ago
https://social.technet.microsoft.com/wiki/contents/articles/28151.using-xml-serialization-with-c-and-sql-server.aspx