Hi !
I have been struggling a lot to call Oracle 9i stored procedure passing Stringbuilder object type from ASP.NET
I am using Visual Studio 2008 Professional, OS: Windows XP and Oracle: 9.2.0.1.0
Following is the procedure:
CREATE or REPLACE PROCEDURE loadCompanyInfo (clobxml IN clob) IS
-- Declare a CLOB variable
ciXML clob;
BEGIN
-- Store the Purchase Order XML in the CLOB variable
ciXML := clobxml;
-- Insert the Purchase Order XML into an XMLType column
INSERT INTO companyinfotbl (companyinfo) VALUES (XMLTYPE(ciXML));
commit;
--Handle the exceptions
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20101, 'Exception occurred in loadCompanyInfo procedure :'||SQLERRM);
END loadCompanyInfo ;
And following is the ASP.net code:
StringBuilder b = new StringBuilder();
b.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
b.Append("<item>");
b.Append("<price>500</price>");
b.Append("<description>some item</description>");
b.Append("<quantity>5</quantity>");
b.Append("</item>");
//Here you'll have the Xml as a string
string myXmlString1 = b.ToString();
//string result;
using (OracleConnection objConn = new OracleConnection("Data Source=testdb; User ID=testuser; Password=pwd1"))
{
OracleCommand objCmd = new OracleCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "loadCompanyInfo";
objCmd.CommandType = CommandType.StoredProcedure;
//OracleParameter pmyXmlString1 = new OracleParameter("pmyXmlString1", new OracleString(myXmlString1));
objCmd.Parameters.Add("myXmlString1", OracleType.clob);
objCmd.Parameters.Add(myXmlString1).Direction = ParameterDirection.Input;
//objCmd.Parameters.Add("result", OracleType.VarChar).Direction = ParameterDirection.Output;
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Label1.Text = "Exception: {0}" + ex.ToString();
}
objConn.Close();
}
***********************************************
When I am trying to execute it, I am getting the following error:
Exception: {0}System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'LOADCOMPANYINFO' ORA-06550: line 1, column 7: PL/SQL: Statement ignored at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at _Default.Button1_Click(Object sender, EventArgs e)
**************************************************
I understand from this that the .net type is not the correct one, but I am not sure how to correct it. I could not find any proper example in any documentation that I came across. Most of the examples give information on how to read but not how to insert XML into Oracle table by calling Stored Procedure.
Can you please help me to solve this problem? I hope that you can help solve this.
Also, can you please give me an example of passing XML document XMLdocument to Oracle Stored procedure.
In both the cases, if you can provide the working code then it would be of great help.
Thanks