Returning Data From an Oracle Stored Procedure Using Dapper

Happy Sunday folks!  It has been a while since my last post, as my family and I started house hunting immediately after the holiday season ended.  I am happy to say we have found a new house and a buyer for our current one, and I should be able to push 1 to 2 articles a month after we settle in to the new place.

Speaking of moving, you may have noticed that this post is about Dapper despite previous ones focusing on Entity Framework.  The reason for this is that I am working on rewriting a legacy application from ASP pages to .NET Core Web API/Angular and the Entity Framework Core driver needed to get the app working with our Oracle database still has not been released at the time of this rewriting.  We need this app to run as a .NET Core application, so utilizing EF6 and targeting the app to the .NET Framework is not an option.  To ensure development efforts are not hampered, I have decided to use Dapper in its place.  As many of you know, Dapper is a database-agnostic micro-ORM that allows developers to quickly map SQL queries to models and perform typical CRUD operations against the database without requiring excess code. 

I took to Steve Michelotti’s Pluralsight course on .NET Micro ORMs to learn how to use this tool, and quickly realized that some of the operations he was having it execute against his SQL Server DB did not behave the same way with my work’s Oracle DB.   Most notably, I was unable to return data from a stored procedure in Oracle the same way he could with SQL Server.  This is because Oracle needed an Out parameter of type sys_refcursor defined so it had a place to store the data to return, and the existing tools in Dapper did not allow me to capture it.  So, like every frustrated developer, I took to Google to see if there was a quick workaround available.  After 2 days of executing work arounds and toying around with additional libraries, I came across this Nuget package called Dapper.Oracle.  I found this to be the best solution, as it is actively being supported (last update was 2 days ago at the time of this writing).  So how can we use this to get data out of an Oracle Stored Procedure?

You can watch me implement the solution in the video below, however if you would like to run the code yourself please clone the GitHub repo associated with this blog and feel free to follow along (you will need to setup the databases yourself).

Let’s start by first comparing two stored procedures used to select games from a database (one from SQL Server, the other from Oracle).

In our SQL Server stored procedure, you can see we are simply defining the procedure name and them selecting all items from table GamesCatalog.

Create procedure [dbo].[GetAllGames] as
	select * from dbo.GameCatalog
go;

However, in our Oracle Stored Procedure defines an out parameter of type sys_refcursor called Games, and then assigns the values from the select statement to it.

create or replace procedure AtlantisGames.GetAllGames (Games out sys_refcursor)
is
begin
	open Games for select * from AtlantisGames.GAMECATALOG;
end GetAllGames;

If we look at the base code I wrote for getting the data from SQL Server, all I am doing is defining a list of type Games, assigning the values returned from stored procedure to it and then returning it. 

GameCatalogRepository.cs

public List<GameCatalog> GetGameCatalog()
{
    List<GameCatalog> games;

    games = this._dbConnection.Query<GameCatalog>(sql:
        "GetAllGames", commandType:
        CommandType.StoredProcedure).ToList();

    return games;
}

Using an integration test called TestGetAllGames I call the GetGameCatalog method from my repository and assert that the result is not null.  The test passes.

GamesTests.cs

[TestClass]
public class GamesTests
{
    [TestMethod]
    public void TestGetAllGames()
    {
        // Arrange
        IGameCatalogRepository repo = CreateRepository();

        // Act 
        var games = repo.GetGameCatalog();

        // Assert
        games.Should().NotBeNull();
    }
}

If I modified the connection string to execute the test against my Oracle database, I would get the below error.

Result Message:	
Test method OracleWithDapperTests.GamesTests.TestGetAllGames threw exception: 
Oracle.ManagedDataAccess.Client.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GETALLGAMES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Remember how I said earlier that the only way to get data back from an Oracle stored procedure was to create a parameter of type sys_refcursor and then assign the values of the query to it?  This has not been defined in our code yet.  We need a dbtype of RefCursor, which is not available to us with vanilla Dapper.  The fix is to download the Dapper.Oracle Nuget package to our project, call it to our repository, and then create an OracleDynamicParameters object where the out parameter can be defined. We then assign the object to param in our Query method.  The results of the method get assigned to our games list, which is once again returned.

GamesRepository.cs

public List<GameCatalog> GetGameCatalog()
{
    List<GameCatalog> games;

    OracleDynamicParameters dynamicParameters = new
        OracleDynamicParameters();

    dynamicParameters.Add(name: ":Games", dbType:
        OracleMappingType.RefCursor, direction:
        ParameterDirection.Output);

    games = this._dbConnection.Query<GameCatalog
        ("atlantisgames.getallgames", param: dynamicParameters,
        commandType: CommandType.StoredProcedure).ToList();


    //games = this._dbConnection.Query<GameCatalog>(sql:
    //"atlantisgames.GetAllGames", commandType:
    //CommandType.StoredProcedure).ToList();


    return games;
}

The test will then work when run. If we put a break point at the return and then run our code in the debugger, we see our values being returned

There you have it!  With an extra Nuget package, we can make Dapper work just as well with Oracle as it does with SQL Server. 

2 thoughts on “Returning Data From an Oracle Stored Procedure Using Dapper”

  1. Very nice explanation about using Dapper with sprocs. I noticed in your Add code you return the number of rows affected instead of the commonly autoincremented Id column that many tables use.

    int rowsAffected = this._dbConnection.Execute(sql, dynamicParameters);

    Often the C# code needs the Id value returned, but I’ve never seen a working Dapper example of this code. Do you have a working example of this? In my own code I just make a 2nd call to the Oracle sequence currval, which is of course two calls which seems wasteful.

    this.db.Execute(“insert statement here”, myPersonObject);
    var id = this.db.Query(“SELECT \”ISEQ$$_123456\”.currval FROM dual “).Single();

    Do

    1. Thanks for the comment Donald! Short answer, no I do not have a working example of this. Most of my experience using Dapper and Oracle was against an established Oracle 11g database, and the tables were not setup by our DBAs to auto-increment. However, it does appear that this is possible to do. This article shows you how to setup auto-increments on your Oracle database so you don’t need to worry about manually setting the ID.
      https://www.databasestar.com/auto_increment-oracle-sql/

      Once you have done that, you should be able to use the answer to this Stack Overflow question to retrieve the ID (bear in mind you will probably be using the OracleDynamicParameters class in place of DynamicParameters if you are following the way I did things in my blog)…
      https://stackoverflow.com/questions/9789737/dapper-and-oracle-crud-issues-how-to

      Please let me know if this helps!

Leave a Reply

Your email address will not be published. Required fields are marked *

I accept that my given data and my IP address is sent to a server in the USA only for the purpose of spam prevention through the Akismet program.More information on Akismet and GDPR.