Executing an Oracle stored procedure within EntityFramework

Question

To cut a long story short: I'm trying to run an Oracle stored procedure within Entity Framework (I know it sounds strange however in general application uses Entity Framework but this particular command can't be processed by EF due to restrictions in modifying key's values).

Procedure has some parameters (only IN) and updates values in table. I tested it by running:

execute PROCEDURE_NAME('parameter1', parameter2 etc.);

It works fine.

My parameters are defined as follows:

OracleParameter param1 = new OracleParameter("PARAM1", OracleDbType.Varchar2, changed.PARAM1, ParameterDirection.Input);

object[] parameters = new object[] { 
                param1,...};

My query is:

string query = "execute PROCEDURE_NAME(:PARAM1,...);";

I'm trying to execute it from C# code. Namely by running:

_context.Database.ExecuteSqlCommand(query, parameters);

I get error ORA-00900: Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.

I think that lack of Procedural Option can't be a reason because creating and running the procedure in a console works.

Unfortunately my tools don't provide profiler, so I can't capture a query produced by Entity Framework. Is there any other way to obtain the executed query? Or maybe you can see any problems with my code?

Answer 1

Try this query string:

string query = "begin PROCEDURE_NAME(:PARAM1,...); end; ";

Answer 2

I've found a solution and it looks as follows:

OracleConnection connection = (Oracle.DataAccess.Client.OracleConnection)_context.Database.Connection;
connection.Open();
OracleCommand cmd = _context.Database.Connection.CreateCommand() as OracleCommand;
cmd.CommandText = "STORED_PROCEDURE_NAME";
cmd.CommandType = CommandType.StoredProcedure;

#region Parameters

//original and changed are just some POCOs
OracleParameter oNameOfParameter = new OracleParameter("oNameOfParameter", OracleDbType.Decimal, original.NameOfParameter, ParameterDirection.Input);
OracleParameter oNameOfParameter2 = new OracleParameter("oNameOfParameter2", OracleDbType.Varchar2, original.NameOfParameter2, ParameterDirection.Input);

OracleParameter NameOfParameter3 = new OracleParameter("nameOfParameter3", OracleDbType.Varchar2, changed.NameOfParameter3, ParameterDirection.Input);
OracleParameter NameOfParameter4 = new OracleParameter("nameOfParameter4", OracleDbType.Decimal, changed.NameOfParameter4, ParameterDirection.Input);

cmd.Parameters.Add(nameOfParameter3);
cmd.Parameters.Add(nameOfParameter4);
cmd.Parameters.Add(oNameOfParameter);
cmd.Parameters.Add(oNameOfParameter2);

#endregion Parameters

var i = cmd.ExecuteNonQuery();
connection.Close();

Procedure itself is obviously stored in database.