Oracle record as input parameter

[StormSupport]

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

[StanislavK]

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.

[StormSupport]

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.

[StanislavK]

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.

[rmontoya]

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

[StanislavK]

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.

[rmontoya]

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

[StanislavK]

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.

[rmontoya]

Are there any workarounds?

[StanislavK]

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]

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