jeudi 30 juin 2016

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.

Aucun commentaire:

Enregistrer un commentaire