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

尝试从Oracle函数获取数字的集合作为输出参数。

我可以调用自定义对象和对象的集合，但我不确定如何获取C＃中的Oracle数字表作为oracle存储过程的输出。

C＃错误消息：'dataSource ='xxxx'schemaName ='ODS'typeName ='NUMBER_TBL''的自定义类型映射未指定或无效

这里是Oracle中的自定义类型number_tbl。

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;

这是C＃.NET代码。

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/20299.html

上一篇: C# Oracle Custom type for TABLE OF NUMBER without custom OBJ

下一篇: Custom object in Oracle stored procedure 11.2.0.4.0