How to execute a stored procedure with NHibernate in .NET Core?

Question

I'm using an Oracle database which has a stored procedure that modifies the value of a client and doesn't return anything.

I'm trying to call it in my controller in a HTTP POST method, like this.

[HttpPost("louerFilm")]
[Authorize]
public IActionResult LouerFilm(LocationRequest locationRequest)
{
    var session = _nHibernateHelper.OpenSession();

    try
    {
        var result = session.CreateSQLQuery("exec pLouerCopieFilm :p1, :p2")
            .SetParameter("p1", locationRequest.IdClient)
            .SetParameter("p2", locationRequest.IdFilm)
            .ExecuteUpdate();

        return StatusCode(200);
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        return StatusCode(471);
    }
}

This code is is not working. Am I using the wrong syntax or is it a problem with the way I'm using stored procedures?

I have tested the procedure directly in my database such as EXEC pLoeurFilm(123, 456) which works correctly.

Answer 1

Was able to correct it by using this following syntax:

var query = Session.CreateSQLQuery("call pLouerFilm(:p1, :p2)")
    .SetParameter("p1", 123);
    .SetParameter("p2", 456);
    .ExecuteUpdate();