Oleksii Aza Oleksii Aza - 5 months ago 31
SQL Question

Oracle stored procedure works with ADO.NET but with OrmLite throws exception?

I have a following stored procedure:

create or replace PROCEDURE PRODUCT_DETAILS(p_code IN VARCHAR2,
cursorParam OUT SYS_REFCURSOR)
IS
BEGIN
OPEN cursorParam FOR
select str_auth_code, str_name
from strs
where str_auth_code = p_code;
END;


How can I call it with OrmLite? I've tryied:

connection.SqlList<Product>(@"EXEC PRODUCT_DETAILS @p_code", new { p_code = code });


but it throws an exception
ORA-01036: illegal variable name/number


I just tried to do it with plain old ADO.NET and it worked:

using (var conn = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "PRODUCT_DETAILS";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("p_code", OracleType.NVarChar).Value = redemptionCode;
cmd.Parameters.Add("cursorParam", OracleType.Cursor);
cmd.Parameters["cursorParam"].Direction = ParameterDirection.Output;

conn.Open();
OracleDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
Console.WriteLine(dr["Name"]);

}
conn.Close();
}


But I can't figure out how to do the same task with OrmLite.

Answer

What you have looks good. If you were concerned about the verbosity of the code, and were using a number of stored procedures, then you could use this extension method to remove some of the repeated code:

Extension Method:

public static class StoredProcExtensions
{
    public static List<T> ExecStoredProcedure<T>(this IDbConnection connection, string procedureName, object parameters = null, string outputCursor = "cursorParam")
    {
        return connection.Exec(c => {
            c.CommandText = procedureName;
            c.CommandType = CommandType.StoredProcedure;

            // Create the parameters from the parameters object
            if(parameters != null)
                foreach(var property in parameters.GetType().GetPublicProperties())
                    c.Parameters.Add(new OracleParameter(property.Name, property.GetValue(parameters)));

            // Add the output cursor
            if(outputCursor != null)
                c.Parameters.Add(new OracleParameter(outputCursor, OracleDbType.RefCursor) { Direction = ParameterDirection.Output });

            // Return the result list
            return c.ExecuteReader().ConvertToList<T>();
        });
    }
}

Usage:

var download = connection.ExecStoredProcedure<ProductDownloads>(
                   "PRODUCT_DETAILS", 
                   new { p_code = redemptionCode }
               );

foreach (var productDownload in download)
{
    Console.WriteLine(productDownload.Name);
}

So the arguments are:

  1. Stored procedure name i.e. PRODUCT_DETAILS

  2. Optional An object of input parameters
    i.e new { p_code = redemptionCode, other = "value" }

  3. Optional The name of the output cursor - defaults to cursorParam

Note: this code is untested, because I don't have Oracle setup, but it does compile, and hopefully goes some way to simplifying your stored procedures.