Oracle record as input parameter

Oracle record as input parameter

Postby StormSupport » Mon 25 Jan 2010 16:07

Hi,

Is it possible to use the library to pass in an OracleTable or an OracleArray object as an input parameter to a Stored Procedure. I keep getting a Null Reference exception when I do that.

Code: Select all
OracleType type = OracleType.GetObjectType("test_type", connection);
var o = new OracleObject(type);
o[type.Attributes[0]] = 1;
OracleTable table = new OracleTable(type);
table.Add(o);
OracleParameter parameter = new OracleParameter("test_parm", OracleDbType.Table);
parameter.Direction = ParameterDirection.Input;
parameter.Value = table;
command.Parameters.Add(parameter);
command.ExecuteNonQuery(); //Exception thrown here
StormSupport
 
Posts: 2
Joined: Mon 25 Jan 2010 15:56

Postby StanislavK » Tue 26 Jan 2010 14:51

The OracleTable constructor needs the nested table type instead of the record type.

Please try replacing
Code: Select all
OracleTable table = new OracleTable(type);

by the following:
Code: Select all
OracleType tableType = OracleType.GetObjectType("test_table_type", connection);
OracleTable table = new OracleTable(tableType);

where "test_table_type" is the custom table type of the table with "test_type" records.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StormSupport » Tue 26 Jan 2010 17:10

Thanks for the reply;

Tried this already with the same result.

We've created a package which defines a simple object type made up of about 5 fields.

We've also created a custom table which holds the above custom type.

We want to be able to create an several instances of this type in C#, and persist them to an instance of the custom table, also in C#. We would use this table object as an input parameter to a stored procedure.

Again, all of these are contained within a single package; there are no global types etc.
StormSupport
 
Posts: 2
Joined: Mon 25 Jan 2010 15:56

Postby StanislavK » Tue 26 Jan 2010 18:00

I send you a sample project (including the script creating the necessary database objects) which worked on our server. Please check that it isn't blocked by your mail filter.

Please specify what has to be changed in the sample to reproduce the problem or send us a small test project.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby rmontoya » Wed 20 Oct 2010 20:43

I have the following types defined:
Code: Select all
  type doc_in_pages_rec is record
      (alias          varchar2(20),
       doc_type       varchar2(5),
       doc_xml        sys.xmltype);

  type doc_in_pages_tab is table of doc_in_pages_rec
      index by binary_integer;

Then I have the following package function:
Code: Select all
function fn_Save(p_documents in doc_in_pages_tab) return int;

Would someone please provide an example of how I could create the appropriate parameter and populate it with a few rows in C# and pass into the function? All the examples I see in the documentation show how to pass in arrays of a single type (string, int, etc) but I'd like to know how to pass in a table with a Record type composed of various types as above.

Thank you
rmontoya
 
Posts: 8
Joined: Wed 20 Oct 2010 20:29

Postby StanislavK » Thu 21 Oct 2010 09:22

Please see the sample below (it supposes that you have an opened OracleConnection object 'con'):
Code: Select all
      // Register custom types (both object and table)
      OracleType type = OracleType.GetObjectType("doc_in_pages_rec", con);
      OracleType table_type = OracleType.GetObjectType("doc_in_pages_tab", con);

      // Create an object of the table type
      OracleTable table = new OracleTable(table_type);
      for (int i = 0; i < 5; i++) {
        var o = new OracleObject(type);
        o[type.Attributes[0]] = "Alias" + i.ToString();
        o[type.Attributes[1]] = "dtype";

        table.Add(o);
      }

      // Configure the command and its input parameter
      OracleCommand com = new OracleCommand();
      com.Connection = con;
      com.CommandType = System.Data.CommandType.StoredProcedure;
      com.CommandText = "fn_Save";
      com.ParameterCheck = true;

      OracleParameter parameter = new OracleParameter("p_documents", OracleDbType.Table);
      parameter.Direction = System.Data.ParameterDirection.Input;
      parameter.Value = table;
      com.Parameters.Add(parameter);

      // Execute the command, get the function result
      com.ExecuteNonQuery();
      Console.Write(com.Parameters["RESULT"].Value.ToString());

However, we have found a problem with registering UDTs with sys.xmltype fields in the latest version of dotConnect for Oracle. We will investigate this issue and inform you about the results here.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby rmontoya » Thu 21 Oct 2010 23:09

I get the following error:

OCI-22303: type ""."DOC_IN_PAGES_REC" not found

My types are defined within a package:

Code: Select all
  CREATE OR REPLACE PACKAGE PA_MYPACKAGE is

...

  type doc_in_pages_rec is record
      (alias          varchar2(20),
       doc_type       varchar2(5),
       doc_xml        sys.xmltype);
       
  type doc_in_pages_tab is table of doc_in_pages_rec
      index by binary_integer;
    
  function fn_Save(p_documents in doc_in_pages_tab) return int;    
    
...
  end PA_MYPACKAGE;


Is there a way to obtain a reference to the record and table types within a package?

I tried to qualify the name but it didn't work either:

Code: Select all
OracleType recordType = OracleType.GetObjectType("PA_MYPACKAGE.doc_in_pages_rec", conn);
rmontoya
 
Posts: 8
Joined: Wed 20 Oct 2010 20:29

Postby StanislavK » Fri 22 Oct 2010 16:26

At the moment, a user-defined type declared in a package cannot be used outside of this package. We are working on the support of such types outside of packages, but cannot provide any timeframe for this.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby rmontoya » Fri 22 Oct 2010 16:28

Are there any workarounds?
rmontoya
 
Posts: 8
Joined: Wed 20 Oct 2010 20:29

Postby StanislavK » Mon 25 Oct 2010 14:29

At the moment, we can only suggest defining these types globally.

We will inform you here when support for the UDTs defined in packages is implemented.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Thu 04 Nov 2010 17:46

We have released the 5.70.190 build of dotConnect for Oracle that includes support for UDTs with xmltype fields. The build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the improvements and fixes available in dotConnect for Oracle 5.70.190, please refer to
http://www.devart.com/forums/viewtopic.php?t=19420
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle