How to call a Stored Procedure inside an oracle package with Entity Framework?

Question

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"}

score 17 · Accepted Answer · answered Feb 25 '14 at 09:40

17

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

answered Feb 25 '14 at 09:40

Devart

119,203
23
166
186

What is the best practice to execute all of procedures in the package? – Amir Feb 25 '14 at 10:12
When working with the Code-First approach, the way we have described above (via the SqlQuery method http://msdn.microsoft.com/en-us/library/gg679117%28v=vs.103%29.aspx ), is the most suitable. The general information regarding creating EF models with stored procedures and working with them is available here http://www.devart.com/dotconnect/oracle/docs/?OracleRoutines.html – Devart Feb 26 '14 at 10:43
@Devart Could you please help to take a look at this question, thanks a lot. http://stackoverflow.com/questions/23236071 – Timeless Apr 23 '14 at 07:49

score 0 · Answer 2 · edited May 23 '17 at 12:34

0

You do not specify the cursor variable.

lst OUT T_CURSOR

That's why you have an error.

You can try the solution from here possible answer.

edited May 23 '17 at 12:34

Community

1
1

answered Feb 24 '14 at 13:38

smnbbrv

23,502
9
78
109

How can i put `lst` parameter into `db.Database.SqlQuery`? – Amir Feb 24 '14 at 14:02
can't [this](http://blog.devart.com/working-with-stored-procedures-and-ref-cursors.html#sp) be helpful? It is about `ref cursor` but I think there is no real problem to make it? – smnbbrv Feb 24 '14 at 14:54

How to call a Stored Procedure inside an oracle package with Entity Framework?

2 Answers2

Linked