Stored procedure invocation

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
bencas

Stored procedure invocation

Post by 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<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

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by 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

Post by 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); 

bencas

Post by 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.

Guest

Post by 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

Paul
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Post by 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 .

Post Reply