如何使用Entity Framework在oracle包中调用存储过程？ [英] How to call a Stored Procedure inside an oracle package with Entity Framework?

问题描述

 创建或替换包HRS.PKG_TRAINING_SP为
 TYPE T_CURSOR IS REF CURSOR; 
 
程序GETPERSONNELTRAINIGLIST（
 staff_Id_in in string，
 base_date_in in string，
 is_current_in in number，
 lst OUT T_CURSOR）; 
结束
  

如何执行上述程序包（ GETPERSONNELTRAINIGLIST ）与实体框架（代码优先）？

注意：我正在使用实体框架6.0（代码优先）和devart EF Provider for Oracle。

更新：
我正在使用以下代码：

  var param1 = new OracleParameter（personnel_Id_in，OracleDbType.VarChar，c5eb5589-8fee-47b6-85ad-261a0307cc16 ParameterDirection.Input）; 
 var param2 = new OracleParameter（base_date_in，OracleDbType.VarChar，1112，ParameterDirection.Input）; 
 var param3 = new OracleParameter（is_current_in，OracleDbType.Number，1，ParameterDirection.Input）; 
 
 var ATests = 
 db.Database.SqlQuery< ATest>（
BEGIN PKG_TRAINING_SP.GETPERSONNELTRAINIGLIST（：personnel_Id_in，：base_date_in，：is_current_in）; end;，
 param1，param2，param3）.ToList（）; 
  

但低于错误提出：

  {ORA-06550：第1行第7列：\\\
PLS-00306：调用'GETPERSONNELTRAINIGLIST'\\\
ORA-06550中的参数的错误数或类型：第1行第7列： \\\
PL / SQL：忽略声明} 
  

解决方案

请以下列方式重写代码：

  var param1 = new OracleParameter（personnel_Id_in，OracleDbType.VarChar，c5eb5589-8fee -47b6-85ad-261a0307cc16，ParameterDirection.Input）; 
 var param2 = new OracleParameter（base_date_in，OracleDbType.VarChar，1112，ParameterDirection.Input）; 
 var param3 = new OracleParameter（is_current_in，OracleDbType.Number，1，ParameterDirection.Input）; 
 var param4 = new OracleParameter（result，OracleDbType.Cursor，ParameterDirection.Output）; 
 
 var ATests = 
 db.Database.SqlQuery< ATest>（
BEGIN PKG_TRAINING_SP.GETPERSONNELTRAINIGLIST（：personnel_Id_in，：base_date_in，is_current_in，：result）; end; ，
 param1，param2，param3，param4）.ToList（）; 
  

此外，我们已在我们的论坛与您联系 http://forums.devart.com/viewtopic.php?t=29019

I have a package in oracle 11g as follow:

CREATE OR REPLACE PACKAGE "HRS.PKG_TRAINING_SP" as
TYPE T_CURSOR IS REF CURSOR;

procedure GETPERSONNELTRAINIGLIST(
        personnel_Id_in in string,
        base_date_in in string,
        is_current_in in number,
        lst OUT T_CURSOR );
end;

How can I execute above procedure package (GETPERSONNELTRAINIGLIST) with Entity Framework (code-first)?

Note: I am using Entity Framwork 6.0 (code-first) and devart EF Provider for Oracle.

Updated: I am using following code:

var param1 = new OracleParameter("personnel_Id_in", OracleDbType.VarChar, "c5eb5589-8fee-47b6-85ad-261a0307cc16", ParameterDirection.Input);
var param2 = new OracleParameter("base_date_in", OracleDbType.VarChar, "1112", ParameterDirection.Input);
var param3 = new OracleParameter("is_current_in", OracleDbType.Number, 1, ParameterDirection.Input);

var ATests =
    db.Database.SqlQuery<ATest>(
    "BEGIN PKG_TRAINING_SP.GETPERSONNELTRAINIGLIST(:personnel_Id_in, :base_date_in, :is_current_in); end;", 
    param1,  param2, param3).ToList();

but below error raised:

{"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'GETPERSONNELTRAINIGLIST'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored"}

解决方案

Please rewrite your code in the following way:

var param1 = new OracleParameter("personnel_Id_in", OracleDbType.VarChar, "c5eb5589-8fee-47b6-85ad-261a0307cc16",  ParameterDirection.Input);
var param2 = new OracleParameter("base_date_in", OracleDbType.VarChar, "1112", ParameterDirection.Input);
var param3 = new OracleParameter("is_current_in", OracleDbType.Number, 1, ParameterDirection.Input);
var param4 = new OracleParameter("result", OracleDbType.Cursor, ParameterDirection.Output);

var ATests =
db.Database.SqlQuery<ATest>(
"BEGIN PKG_TRAINING_SP.GETPERSONNELTRAINIGLIST(:personnel_Id_in, :base_date_in, :is_current_in, :result); end;", 
param1,  param2, param3, param4).ToList();

Also, we have contacted you at our forum http://forums.devart.com/viewtopic.php?t=29019

这篇关于如何使用Entity Framework在oracle包中调用存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！