How to get a string from a stored procedure with Oracle EntityFramework Core?

FonsecaMR

This stored procedure is just to demonstrate a problem.

create or replace PROCEDURE SP_TEST_CSHARP (word IN varchar2,upperword OUT varchar2) AS

begin

        select upper(word) into upperword from dual;

       

end;

I created this model,

    public partial class rsProcedure

    {

            public string upperword{ get; set; }

    }

I tried this:

            OracleParameter word = new OracleParameter("word",OracleDbType.Varchar2, ParameterDirection.Input);

             word.Value = "marcio";

            OracleParameter upperword = new OracleParameter("upperword",OracleDbType.Varchar2, ParameterDirection.Output);

            var result = _dbContext.rsProcedures.FromSqlRaw("begin SP_TEST_CSHARP(:word, :upperword); end;", word, upperword);

So i get this error message:

System.InvalidOperationException: The required column 'upperword' was not present in the results of a 'FromSql' operation.

Could anyone tell me what I'm doing wrong?

Thank you very much

Alex Keh-Oracle

I don't believe EF Core itself supports stored procedures that return non-entity data yet. See this GitHub Issue that tracks EF Core stored procedure mapping support. The "General data retrieval using stored procedures" is being tracked, but not completed yet by Microsoft.

I tried your test case and a few variations of it myself with no luck. You will have to use the stored procedure outside of EF Core, then return the data to your EF Core app.

Alessandro Rosá

With EF Core 2 and 3.1 I was able to do what you're asking in another way (without using a model).

var inParamString1 = new OracleParameter("InParamString1", OracleDbType.Int64, ParameterDirection.Input);
var inParamString2 = new OracleParameter("InParamString2", OracleDbType.Varchar2, ParameterDirection.Input);
var outParamResult =
   new OracleParameter("OutParamResult", OracleDbType.Varchar2, 50, output, ParameterDirection.Output);

inParamString1.Value = 1;
inParamString2.Value = "asdasd";
var result = await dbContext.Database.ExecuteSqlCommandAsync(
   "BEGIN  PROCEDURENAME (:InParamString1, :InParamString2, :OutParamResult); END;",
  inParamString1, inParamString2, outParamResult);

var hereYourStringFromTheProcedure= outParamResult.Value.ToString();

If you updated Oracle.EntityFrameworkCore to the latest 3.19.80 it would be better to use the new method

await dbContext.Database.ExecuteSqlRawAsync( 

"BEGIN  PROCEDURENAME (:InParamString1, :InParamString2, :OutParamResult); END;",

  inParamString1, inParamString2, outParamResult);

I think it would be doable also by defining the model as you did but I didn't have enough time to make it work.