Stored procedure invocation

Stored procedure invocation

Postby bencas » Wed 01 Jun 2005 21:29

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{
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
bencas
 

Postby Paul » Thu 02 Jun 2005 07:21

What version of OraDirect .NET do you use? What is a date of release?
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Paul » Thu 02 Jun 2005 08:58

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);
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby bencas » Thu 02 Jun 2005 10:29

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

Postby Guest » Thu 02 Jun 2005 15:26

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
Guest
 

Postby Paul » Thu 02 Jun 2005 15:37

OraDirect cannot receive information about type from package. It is a known problem of OCI.
You must use types .
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06


Return to dotConnect for Oracle