Showing posts with label stored procedures. Show all posts
Showing posts with label stored procedures. Show all posts

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)
is
begin
      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.