How to get a string from a stored procedure with Oracle EntityFramework Core? — oracle-tech

    Forum Stats

  • 3,708,745 Users
  • 2,241,117 Discussions
  • 7,840,574 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

FonsecaMRFonsecaMR Posts: 3
edited September 2020 in ODP.NET

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

FonsecaMR

Answers

  • Alex Keh-OracleAlex Keh-Oracle Posts: 2,720 Employee
    edited September 2020

    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.

    FonsecaMRFonsecaMR
  • Alessandro RosáAlessandro Rosá Posts: 1 Newbie
    edited September 2020

    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.

    Alex Keh-Oracle
Sign In or Register to comment.