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);
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;}
}
I followed this guide to define .net entity properly but still no luck with passing my list:
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)
Best Answer
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!