How to call a Stored Procedure inside an oracle package with Entity Framework?
22,045
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
Author by
Amir
Professional Developer for Win and Web. I am willing to always learn something new and try improve it.
Updated on July 09, 2022Comments
-
Amir almost 2 years
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"}
-
Amir about 10 yearsHow can i put
lst
parameter intodb.Database.SqlQuery
? -
smnbbrv about 10 yearscan't this be helpful? It is about
ref cursor
but I think there is no real problem to make it? -
Amir about 10 yearsWhat is the best practice to execute all of procedures in the package?
-
Devart about 10 yearsWhen working with the Code-First approach, the way we have described above (via the SqlQuery method 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 devart.com/dotconnect/oracle/docs/?OracleRoutines.html
-
Timeless about 10 years@Devart Could you please help to take a look at this question, thanks a lot. stackoverflow.com/questions/23236071