When I tried to execute an Oracle stored procedure with Entity Framework Core, I get this exception :
No mapping to a relational type can be found for the CLR type 'OracleParameter[]
I tried ExecuteSqlCommand
and FromSQL
methods, both cause the same exception.
Oracle stored procedure code :
CREATE OR REPLACE Procedure GET_DEPT_PROC (
p_ParentSiteId number,
P_SiteId number,P_LoggedUserId number, P_curParam OUT sys_REFCURSOR) as
Begin
OPEN curParam FOR
select 1 Id, 2 EntityAlmostOnRenewalTrans, 3 EntityVerifiedTrans, 4
EntityCompletedTrans, 5 EntityClosedTrans, 6 MyVerifiedTrans, 7
MyCreatedTrans, 8 MyDraftedTrans, 9 MyRejectedTrans FROM DUAL;
End;
C# method to execute this stored procedure:
public virtual DashBoardData GetDashBoardData(short parentSiteId, short siteId, int loggedUserId)
{
var parameters = new OracleParameter[]
{
new OracleParameter("@p_ParentSiteId", parentSiteId),
new OracleParameter("@p_SiteId", siteId),
new OracleParameter("@p_LoggedUserId", loggedUserId),
new OracleParameter("@p_curParam", OracleDbType.RefCursor, ParameterDirection.Output)};
string sql = "EXEC GET_DEPT_PROC(@p_ParentSiteId,@p_SiteId,@p_LoggedUserId:@p_curParam);";
var res = XDB.FromSql<DashBoardData>(base.context, sql, parameters).ToList()[0];
}
FromSQL Method in XDB class:
public static IQueryable<TEntity> FromSql<TEntity>(DbContext EFContext,RawSqlString sql, IEnumerable<OracleParameter> op) where TEntity : class
{
return EFContext.Set<TEntity>().FromSql(sql, op);
}
Best Answer
Here is the answer with example:
.: Oracle Stored Procedure:
.: Execute SP in Oracle:
.: Execute in Code using FromSQL Method: