在EntityFramework中执行Oracle存储过程 [英] Executing an Oracle stored procedure within EntityFramework

问题描述

简而言之:我正在尝试在Entity Framework中运行Oracle存储过程(我知道这听起来很奇怪，但是在一般应用程序中使用Entity Framework，但是由于限制，EF无法处理此特定命令修改键的值).

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

过程具有一些参数(仅IN)，并更新表中的值.我通过运行以下命令进行了测试:

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

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

工作正常.

我的参数定义如下:

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

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

我的查询是:

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

我正在尝试从C#代码执行它.即通过运行:

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

_context.Database.ExecuteSqlCommand(query, parameters);

我收到错误ORA-00900:原因:该语句未被识别为有效的SQL语句.如果未安装过程选项，并且发出要求此选项的SQL语句(例如CREATE PROCEDURE语句)，则可能发生此错误.您可以通过启动SQL * Plus来确定是否安装了过程选件.如果未显示PL/SQL标语，则表示未安装该选件.

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.

我认为缺少Procedural Option可能不是原因，因为在控制台中创建和运行该过程即可.

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

不幸的是，我的工具没有提供分析器，因此我无法捕获由Entity Framework生成的查询.还有其他方法可以获取执行的查询吗?也许您可以看到我的代码有任何问题?

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?

推荐答案

我找到了一个解决方案，它看起来如下:

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.

这篇关于在EntityFramework中执行Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！