C# Oracle Custom type for TABLE OF NUMBER without custom OBJ

Trying to get Collection of numbers as an output parameter from Oracle function.

I was able to call custom object and collection of the objects, but I am not sure how to get oracle table of number in C# as output of oracle stored procedure.

C# Error Message: Custom type mapping for ' dataSource='xxxx' schemaName='ODS' typeName='NUMBER_TBL'' is not specified or is invalid

Here is the custom type number_tbl in Oracle.

create or replace TYPE number_tbl IS TABLE OF NUMBER(38,4);
Oracle Package Function

    FUNCTION get_queue_msgs_fun_4(out_number_tbl      OUT     number_tbl )  RETURN NUMBER IS
    CURSOR number_cur 
    IS
            SELECT 101
            FROM dual
        union 
            SELECT 102
            FROM dual
        union
            SELECT 103
            FROM dual  ;

    v_number_tbl number_tbl := number_tbl(); 
  begin
    open number_cur;
    fetch number_cur BULK COLLECT into v_number_tbl;
    close number_cur;
    out_number_tbl := v_number_tbl;
    return 0;
  end;

Here is the C# .NET code.

static void Main(string[] args)
    {
        using (var connection =
            new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service))); User ID=ODS; Password=xxxyyy"))
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = " ods.read_queue_pkg.get_queue_msgs_fun_4";
                command.BindByName = true;                    

                command.Parameters.Add("Return_Value", OracleDbType.Int32, ParameterDirection.ReturnValue);

                var parameter = command.CreateParameter();
                parameter.Direction = ParameterDirection.Output;
                parameter.ParameterName = "out_number_tbl";
                parameter.OracleDbType = OracleDbType.Object;
                parameter.UdtTypeName = "ODS.NUMBER_TBL";
                command.Parameters.Add(parameter);

                using (var dr = command.ExecuteReader())
                {
                    NUMBER_TBL rtnval2 = (NUMBER_TBL)command.Parameters[1].Value;
                }                    

            }
        }
    }

    [OracleCustomTypeMapping("ODS.NUMBER_TBL")]
    public class NUMBER_TBL
    {

    }

    [OracleCustomTypeMapping("ODS.NUMBER_OBJ")]
    public class NUMBER_OBJ : CustomTypeBase<NUMBER_OBJ>
    {
        [OracleObjectMapping("NUMBER")]
        public Int32 number;

        public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt)
        {
            OracleUdt.SetValue(connection, pointerUdt, "NUMBER", number);
        }

        public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt)
        {
            number = (Int32)OracleUdt.GetValue(connection, pointerUdt, "NUMBER");
        }
    }

链接地址: http://www.djcxy.com/p/20300.html

上一篇: 如何设置html链接的到期日期

下一篇: C＃Oracle Oracle自定义类型，用于没有自定义OBJ的TABLE NUMBER