Devart Forums

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

What version of OraDirect .NET do you use? What is a date of release?

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.

Thanks for the quick response, I will try your code out and let you know if I am successful.

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?

Is it

.. ?

Also, does the type need to be specified in the database, or just in the package?

Thanks

OraDirect cannot receive information about type from package. It is a known problem of OCI.
You must use types .