Calling Oracle stored procedure using Entity Framework with output parameter?

Question

I have a simple Oracle stored procedure that gets three parameters passed in, and has one output parameter:

CREATE OR REPLACE PROCEDURE RA.RA_REGISTERASSET
(
    INPROJECTNAME IN VARCHAR2
    ,INCOUNTRYCODE IN VARCHAR2
    ,INLOCATION IN VARCHAR2
    ,OUTASSETREGISTERED OUT VARCHAR2
)
AS
BEGIN
  SELECT 
      INPROJECTNAME || ', ' || INLOCATION || ', ' || INCOUNTRYCODE
  INTO
      OUTASSETREGISTERED
  FROM
      DUAL;     
END RA_REGISTERASSET;

I am trying to use Entity Framework 6.1 to get back the OutAssetRegistered value, however, I get a null after calling SqlQuery with no exception:

public class CmdRegisterAssetDto
{
        public string inProjectName { get; set; }
        public string inCountryCode { get; set; }
        public string inLocation { get; set; }
        public string OutAssetRegistered { get; set; }
}

//------------------------------------------------------------

string projectName = "EXCO";
string location = "ANYWHERE";
string countryCode = "XX";

using (var ctx = new RAContext())
{
    var projectNameParam = new OracleParameter("inProjectName", OracleDbType.Varchar2, projectName, ParameterDirection.Input);
    var countryCodeParam = new OracleParameter("inCountryCode", OracleDbType.Varchar2, countryCode, ParameterDirection.Input);
    var locationParam = new OracleParameter("inLocation", OracleDbType.Varchar2, location, ParameterDirection.Input);
    var assetRegisteredParam = new OracleParameter("OutAssetRegistered", OracleDbType.Varchar2, ParameterDirection.Output);

    var sql = "BEGIN RA.RA_RegisterAsset(:inProjectName, :inCountryCode, :inLocation, :OutAssetRegistered); END;";
    var query = ctx.Database.SqlQuery<CmdRegisterAssetDto>(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam
    );

    assetRegistered = (string)assetRegisteredParam.Value;
}

I have been battling to get this to work to no avail, have checked different blogs, all the other crud operations work, can anyone please assist and direct me where I am going wrong?

Answer 1

In this case, you shouldn't be calling:

var query = ctx.Database.SqlQuery<CmdRegisterAssetDto>(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam);

But instead be calling:

var result = ctx.Database.ExecuteSqlCommand(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam);

Notice that the only effective difference is that SqlQuery<CmdRegisterAssetDto> was replaced with ExecuteSqlCommand. This also means that the DTO is unnecessary. Otherwise, your code looks like it should work. Here's your original code in its entirety with the changes I mentioned:

string projectName = "EXCO";
string location = "ANYWHERE";
string countryCode = "XX";

using (var ctx = new RAContext())
{
    var projectNameParam = new OracleParameter("inProjectName", OracleDbType.Varchar2, projectName, ParameterDirection.Input);
    var countryCodeParam = new OracleParameter("inCountryCode", OracleDbType.Varchar2, countryCode, ParameterDirection.Input);
    var locationParam = new OracleParameter("inLocation", OracleDbType.Varchar2, location, ParameterDirection.Input);
    var assetRegisteredParam = new OracleParameter("OutAssetRegistered", OracleDbType.Varchar2, ParameterDirection.Output);

    var sql = "BEGIN RA.RA_RegisterAsset(:inProjectName, :inCountryCode, :inLocation, :OutAssetRegistered); END;";
    var result = ctx.Database.ExecuteSqlCommand(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam);

    assetRegistered = (string)assetRegisteredParam.Value;
}

To prove my theory, I reproduced the null behavior that you're experiencing and then made that one change. It hung for a bit (probably to let EF kick into gear), but then executed quickly every time thereafter. In each case, I found a value waiting in the out parameter.

If anyone out there is running into trouble, there's a longhand variation that takes care of the scripting details for you:

string projectName = "EXCO";
string location = "ANYWHERE";
string countryCode = "XX";

using (var ctx = new RAContext())
using (var cmd = ctx.Database.Connection.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "RA.RA_REGISTERASSET";

    var projectNameParam = new OracleParameter("inProjectName", OracleDbType.Varchar2, projectName, ParameterDirection.Input);
    var countryCodeParam = new OracleParameter("inCountryCode", OracleDbType.Varchar2, countryCode, ParameterDirection.Input);
    var locationParam = new OracleParameter("inLocation", OracleDbType.Varchar2, location, ParameterDirection.Input);
    var assetRegisteredParam = new OracleParameter("OutAssetRegistered", OracleDbType.Varchar2, ParameterDirection.Output);
    cmd.Parameters.AddRange(new[] { projectNameParam, countryCodeParam, locationParam, assetRegisteredParam });

    cmd.Connection.Open();
    var result = cmd.ExecuteNonQuery();
    cmd.Connection.Close();

    assetRegistered = (string)assetRegisteredParam.Value;
}

As an afterthought, you technically could go with your original solution if you invoked the query immediately after (i.e. query.FirstOrDefault()). The return value of the query would always be null, but your out parameter would at least get populated. This is because EF queries use deferred execution.