CODEWITHSUNDEEP
c#sqloracle-databaseentity-framework
Arkadiusz Kałkus
Arkadiusz Kałkus

Reputation: 18443

Executing an Oracle stored procedure within EntityFramework

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?

Upvotes: 6

Views: 9219

Answers (3)

Luiz Baião
Luiz Baião

Reputation: 11

EntityERP context = new EntityERP (); // is a context map from entity

context.Database.ExecuteSqlCommand("BEGIN  STORED_PROCEDURE_NAME; END; ");

This resolve.

Upvotes: 0

Arkadiusz Kałkus
Arkadiusz Kałkus

Reputation: 18443

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.

Upvotes: 5

Sohaty
Sohaty

Reputation: 195

Try this query string:

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

Upvotes: 9

Related Questions