Thursday, October 17, 2013

Calling Oracle Store Procedure with Output Parameters on EntityFramework 4

We were having a lot of issues when trying to run a stored procedure with output parameters in Oracle, using Entity Framework 4 Library.

Finally, we came up with the following solution:

create or replace procedure dummy_proc
    (dummy1 in number, dummy2 in varchar2,
 dummy3 out number, dummy4 out varchar2)
      dummy3 := 1508 + dummy1;
  dummy4 := 'This is just sad! -> ' || dummy2;
end dummy_proc;

And its correspondent .NET call:

Entities e = new Entities();
OracleParameter o1 = new OracleParameter("dummy1", OracleDbType.Int32);
OracleParameter o2 = new OracleParameter("dummy2", OracleDbType.Varchar2);
OracleParameter o3 = new OracleParameter("dummy3", OracleDbType.Int32);
OracleParameter o4 = new OracleParameter("dummy4", OracleDbType.Varchar2);
o1.Direction = System.Data.ParameterDirection.Input; o1.Value = v;
o2.Direction = System.Data.ParameterDirection.Input; o2.Value = "test"; o2.Size = 1000;
o3.Direction = System.Data.ParameterDirection.Output; 
o4.Direction = System.Data.ParameterDirection.Output; o4.Size = 1000;
e.ExecuteStoreCommand("begin dummy_proc(:dummy1, :dummy2, :dummy3, :dummy4); end;", o1, o2, o3, o4); 

Additionally, you might have to add a reference to Oracle's ODAC libraries to your project.