How to pass xml as a parameter in Oracle?

Question

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.

Hariharan Krishnamoorthi · Answer

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 Jha · Answer

Vignesh The link explains about xml using SQL Server but I am looking for Oracle implementation.

Sundar · Answer

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.VarChar255).Value = userName; DbUpdateCommand.ExecuteNonQuery();

Gokhul Varman · Answer

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

Vignesh Mani · Answer

https://social.technet.microsoft.com/wiki/contents/articles/28151.using-xml-serialization-with-c-and-sql-server.aspx