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

If you need to pass in and/or out a date:

        public DateTime GetDate(string dateIn)
        {
            Regex regex = new Regex(@"(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z)");

            if (!regex.IsMatch(dateIn))
            {
                throw new ArgumentException($"dateIn must be given in the YYYY-MM-DD\"T\"hh24:mi:ss\"Z\" format. " +
                    $"Value given: {dateIn}");
            }

            OracleParameter dateInParam = new OracleParameter("DATE_IN_", dateIn);
            createdParam.OracleDbType = OracleDbType.TimeStampTZ;
            OracleParameter dateOutParam= new OracleParameter("DATE_OUT_", OracleDbType.Date, System.Data.ParameterDirection.Output);

            var sql = "BEGIN " +
                $"Get_Date(:DATE_IN_, TO_TIMESTAMP_TZ('{dateIn}','YYYY-MM-DD\"T\"hh24:mi:ss\"Z\"'), :DATE_OUT_ ); " +
                "END;";

#pragma warning disable EF1000 // Possible SQL injection vulnerability.
            context.Database.ExecuteSqlCommand(sql, dateInParam, dateOutParam);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.

            return (DateTime)(OracleDate)dateOutParam.Value;
        }