How to set user-defined-type as Oracle SP input parameter in .NET

摘自：http://forums.oracle.com/forums/thread.jspa?threadID=386484&tstart=0
        http://forums.oracle.com/forums/thread.jspa?threadID=911710&tstart=29

We have an Oracle SP that is being called in C#.

Now we try to pass a user defined type (UDT) as an input parameter to the SP;
however we did not find a solution.
It is possible to use Oracle ref_cursor as SP output(!) parameters but not input parameters, since ODP.NET does support input parameters until Oracle Version 10. (As for ADO.NET, we have not found any examples.)

There might be a solution with using Oracle AnyType dataType or some other strange but brilliant developer idea; but we still don't know how to proceed....

Example Oracle - UDT:
TYPE ERROR_INFO
AS OBJECT
(
CODE NUMBER(5),
DESCRIPTION VARCHAR2(1000)
)

EXAMPLE SP:
FUNCTION SF_TEST1(error_in IN error_info DEFAULT NULL)

RETURN BOOLEAN;

 

It possible to use user-defined-type as SP input parameter in .NET using new ODP.net 11.1.6 that support UDT.
Here my sample:
I created simple object type

create or replace type TYP_address as object
(
-- Attributes
address VARCHAR2(500),
ZIPCODE Varchar2(5)
)

then i created simple sp using as input parameter an object in order to map it on relational table:

create table TAB_ADDRESS
(
ADDRESS VARCHAR2(200),
ZIPCODE VARCHAR2(5)
);

create or replace procedure test_udt_sp(obj_address IN TYP_address ) is
begin
INSERT INTO tab_address(address,ZIPCODE) VALUES(obj_address.address,obj_address.ZIPCODE);
COMMIT;
end test_udt_sp;

After i created simple console application using C# and ODP.net.
In order to map Oracle object TYP_address i created custum class TYP_ADDRESS (this could be done automatically with new ODT). Here the C# code autogenerated by ODT:

//------------------------------------------------------------------------------
// <autogenerated>
// This code was generated by a tool.
// Runtime Version: 1.1.4322.2407
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </autogenerated>
//------------------------------------------------------------------------------

using System;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class TYP_ADDRESS : INullable, IOracleCustomType {

private bool m_IsNull;

private string m_ZIPCODE;

private string m_ADDRESS;

public TYP_ADDRESS() {
// TODO : Add code to initialise the object
}

public TYP_ADDRESS(string str) {
// TODO : Add code to initialise the object based on the given string
}

public virtual bool IsNull {
get {
return this.m_IsNull;
}
}

public static TYP_ADDRESS Null {
get {
TYP_ADDRESS obj = new TYP_ADDRESS();
obj.m_IsNull = true;
return obj;
}
}

public string ZIPCODE {
get {
return this.m_ZIPCODE;
}
set {
this.m_ZIPCODE = value;
}
}

public string ADDRESS {
get {
return this.m_ADDRESS;
}
set {
this.m_ADDRESS = value;
}
}

public virtual void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt) {
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ZIPCODE", this.ZIPCODE);
Oracle.DataAccess.Types.OracleUdt.SetValue(con, pUdt, "ADDRESS", this.ADDRESS);
}

public virtual void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, System.IntPtr pUdt) {
this.ZIPCODE = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "ZIPCODE")));
this.ADDRESS = ((string)(Oracle.DataAccess.Types.OracleUdt.GetValue(con, pUdt, "ADDRESS")));
}

public override string ToString() {
// TODO : Return a string that represents the current object
return "";
}

public static TYP_ADDRESS Parse(string str) {
// TODO : Add code needed to parse the string and get the object represented by the string
return new TYP_ADDRESS();
}
}

// Factory to create an object for the above class

public class TYP_ADDRESSFactory : IOracleCustomTypeFactory {

public virtual IOracleCustomType CreateObject() {
TYP_ADDRESS obj = new TYP_ADDRESS();
return obj;
}
}

Then i wrote simple C# code to call the sp using the UDT:

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

class ObjectSample
{
static void Main(string[] args)
{
// cahnge your connection string
string constr = "user id=xxxxx;password=xxxxxx;data source=xxxxxx";

TYP_ADDRESS addr = new TYP_ADDRESS();
addr.ADDRESS = "via emilia ponente 216";
addr.ZIPCODE = "40133";

// Establish a connection to Oracle
OracleConnection con = new OracleConnection(constr);
con.Open();

// call sp passing object addr
// the sp map the object on relational table
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "test_udt_sp";
OracleParameter param1 = new OracleParameter();

param1.OracleDbType = OracleDbType.Object;
param1.Direction = ParameterDirection.InputOutput;

// Note: The UdtTypeName is case-senstive
param1.UdtTypeName = "TYP_ADDRESS";
param1.Value = addr;

cmd.Parameters.Add(param1);

try
{

cmd.ExecuteNonQuery();

}
catch(Exception exc)
{
Console.WriteLine(exc.Message);
}
finally
{

cmd.Dispose();
con.Close();
con.Dispose();
}
}

}

 

 for example:
you have TABLE OF TYP_ADDRESS and use this as Output-Parameter.

So i would code it like that -- but it doesn't work
...
OracleParameter param1 = new OracleParameter();

param1.OracleDbType = OracleDbType.Object;
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param1.Direction = ParameterDirection.Output;
param1.Size = 3; //Means 3 fields in the array

// Note: The UdtTypeName is case-senstive
param1.UdtTypeName = "TYP_ADDRESS";
param1.Value = null;
...
cmd.ExecuteNonQuery(); // -->
//Get an Exception: Invalid number or Types for the Parameters

hope, you can help me a little bit - thx in advance