If i have the following stored procedure:
PROCEDURE XMLTABLE_SELECT_NOCURSOR (id OUT T_ID , xml OUT T_XML)
IS
CURSOR doc_cur IS
SELECT
NUM_COL,
XMLTYPE_COL
FROM
XML_TABLE;
recCount NUMBER DEFAULT 0 ;
BEGIN
FOR docCurRec IN doc_cur LOOP
recCount := recCount + 1 ;
id(recCount):= docCurRec.NUM_COL ;
xml(recCount):= docCurRec.XMLTYPE_COL ;
END LOOP;
END XMLTABLE_SELECT_NOCURSOR;
How would I invoke it from C#?
Currently i'm using the following code -
CoreLab.Oracle.OracleCommand cmd = new CoreLab.Oracle.OracleCommand("CURSPKG.XMLTABLE_SELECT_NOCURSOR", ConnectionMgr.connOraDirect);
CoreLab.Oracle.OracleParameter p = new CoreLab.Oracle.OracleParameter("id", CoreLab.Oracle.OracleDbType.Number);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
p = new CoreLab.Oracle.OracleParameter("xml", CoreLab.Oracle.OracleDbType.Xml);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
CoreLab.Oracle.OracleDataReader reader = cmd.ExecuteReader();
cmd.FetchSize = 10;
for (int i=0;i<Int16.Parse(txtBulkInsertNumber.Text);i++)
{
reader.Read();
decimal x= (decimal)reader[0];
CoreLab.Oracle.OracleXml doc = reader.GetOracleXml(1);
XmlDocument xmlDoc = doc.GetXmlDocument();
}
However I receive an {"Object reference not set to an instance of an object." } System.NullReferenceException.
Could somebody please enlighten me?
Thanks,
Ben
Stored procedure invocation
This error happen if you do not specify ObjectTypeName for object (XMLTYPE) parameter. System.NullReferenceException is not correct error in this situation. We will correct this in the next build. In fact you must use parameters of type T_ID, T_XML (We suppose that T_ID, T_XML are VARRAY). Please use the following code.
Code: Select all
CoreLab.Oracle.OracleParameter p = new CoreLab.Oracle.OracleParameter("id", CoreLab.Oracle.OracleDbType.Object);
p.Direction = ParameterDirection.Output;
p.ObjectTypeName = "TEST.T_ID";
cmd.Parameters.Add(p);
p = new CoreLab.Oracle.OracleParameter("xml", CoreLab.Oracle.OracleDbType.Object);
p.Direction = ParameterDirection.Output;
p.ObjectTypeName = "TEST.T_XML";
cmd.Parameters.Add(p);
Hi, unfortunately the ObjectTypeName did not work for me. What exactly is the format of the property that i need to set?CoreLab.Oracle.OracleParameter p = new CoreLab.Oracle.OracleParameter("id", CoreLab.Oracle.OracleDbType.Object);
p.Direction = ParameterDirection.Output;
p.ObjectTypeName = "TEST.T_ID";
cmd.Parameters.Add(p);
p = new CoreLab.Oracle.OracleParameter("xml", CoreLab.Oracle.OracleDbType.Object);
p.Direction = ParameterDirection.Output;
p.ObjectTypeName = "TEST.T_XML";
cmd.Parameters.Add(p);
Is it
.. ?
Also, does the type need to be specified in the database, or just in the package?
Thanks