从C#将表类型对象作为输入参数传递给Oracle中的存储过程 [英] Pass table-type object as input parameter to Stored Procedure in Oracle from C#

问题描述

我有接受表类型作为输入参数的存储过程. 如何通过C#代码传递对象?

I have stored procedure which accepts table-type as input parameter. How to pass object from c# code?

create or replace TYPE CUSTOM_TYPE AS OBJECT 
( 
  attribute1 VARCHAR(10),
  attribute2 VARCHAR(10)
);

create or replace TYPE CUSTOM_TYPE_ARRAY AS TABLE OF CUSTOM_TYPE;

PROCEDURE SP_TEST
(
  P_TABLE_IN IN CUSTOM_TYPE_ARRAY,
  P_RESULT_OUT OUT SYS_REFCURSOR 
) AS 
BEGIN
  OPEN P_RESULT_OUT FOR
    SELECT  ti.attribute1, ti.attribute2, ti.attribute3
    FROM  TABLE(P_TABLE_IN) ea inner join MYTABLE ti on ea.attribute1 = ti.attribute1 and ea.attribute2 = ti.attribute2;
END SP_TEST;

并尝试添加这样的参数:

And trying to add parameter like this:

var oracleParam = new OracleParameter();
oracleParam.OracleDbType = OracleDbType.Array;
oracleParam.Direction = ParameterDirection.Input;
oracleParam.ParameterName = "P_TABLE_IN";
oracleParam.Value = entities;
oracleCommand.Parameters.Add(oracleParam); 

实体是实体的自定义集合的实例(具有定义的oracle属性映射)

Where entities is an instance of custom collection of entities (with defined oracle attributes mapping)

public class EntityUdt
{
    [OracleObjectMappingAttribute("attribute1")]
    public string attribute1 {get;set;}
    [OracleObjectMappingAttribute("attribute2")]
    public string attribute2 {get;set;}
}

我遵循了本指南正确定义.net实体，但通过传递我的列表仍然没有运气:

I followed this guide to define .net entity properly but still no luck with passing my list:

System.InvalidOperationException由用户代码未处理
HResult = -2146233079
Message ='EntityUdt :: attribute1 :: OracleObjectMappingAttribute' 无法设置为无效值'attribute1'
源= Oracle.DataAccess StackTrace: 在Oracle.DataAccess.Types.OracleUdtDescriptor.DescribeCustomType(Object customTypeFactory)

System.InvalidOperationException was unhandled by user code
HResult=-2146233079
Message='EntityUdt::attribute1::OracleObjectMappingAttribute' cannot be set to an invalid value of 'attribute1'
Source=Oracle.DataAccess StackTrace: at Oracle.DataAccess.Types.OracleUdtDescriptor.DescribeCustomType(Object customTypeFactory)

推荐答案

我花了数小时试图找出问题所在，事实证明，由oracle属性定义的映射应具有与属性名称不同的名称.

I spent hours trying to identify the issue and it turned out that mapping defined by oracle attributes should have different name than property name.

所以我只需将所有映射属性都大写即可，现在就可以使用！

So I just make all the mapping attributes uppercase and it works now!

    [OracleObjectMappingAttribute("ATTRIBUTE1")]
    public string attribute1 { get; set; }
    [OracleObjectMappingAttribute("ATTRIBUTE2")]
    public string attribute2 { get; set; }

这篇关于从C#将表类型对象作为输入参数传递给Oracle中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！