3

I am upgrading my application to Oracle.ManagedDataAccess. I want to pass an array of objects to a stored procedure.

Following is my SP:

CREATE OR REPLACE TYPE MATCHING_CRITERIA_LIST IS TABLE OF CRITERIA;

CREATE OR REPLACE PROCEDURE GET_FILTERED_EMPLOYEE (
IN_CRITERIA_LIST   IN       MATCHING_CRITERIA_LIST,
CUR_OUT            OUT      sys_refcursor
)
AS
OPEN CUR_OUT FOR
--My Select Query--
END;

I am trying to execute SP through C# .NET, and getting error.

var param = new OracleParameter() { ParameterName = "IN_CRITERIA_LIST", Direction = ParameterDirection.Input, CollectionType = OracleCollectionType.PLSQLAssociativeArray, DbType = DbType.Object}; 
param.Value = /*Some Array */;
var parameters = new List<IDbDataParameter>() {param, new OracleParameter("CUR_OUT", OracleDbType.RefCursor) { Direction = ParameterDirection.Output }};
reader = dbConn.ExecuteReaderWithParams("GET_FILTERED_EMPLOYEE", parameters, CommandType.StoredProcedure);

Error:

Message=Unsupported column datatype Source=Oracle Data Provider for .NET, Managed Driver

This is not a duplicate of this as I am trying to pass an array of user defined object and not primitive types.

shruti singh
  • 146
  • 11
  • No it isnot a duplicate. I am trying to pass array of Objects. The link you shared is of passing array of strings/int. The above code works for me in case of int, but not when I pass an array of class object. – shruti singh Dec 09 '17 at 04:09

0 Answers0