I have an Oracle stored procedure that I am trying to call with EF code-first (
DbContext) using the DevArt Oracle drivers (latest version). I don't know why this has to be so hard (I wish I was using SQL Server), but I cannot make it work. I am doing what is described in this post
I followed the answer that was supplied by the DevArt team themselves, and I still get an Oracle exception, albeit slightly different:
ora-01036 illegal variable name/number
Here's the signature of my stored procedure:
create or replace PROCEDURE GP_PARTICIPANTDETAILS ( p_contracts_list VARCHAR2, p_participant_type CHAR DEFAULT NULL, p_dob_range_begin DATE DEFAULT NULL, p_dob_range_end DATE DEFAULT NULL, p_part_name VARCHAR DEFAULT NULL, p_ssn VARCHAR DEFAULT NULL, p_status_list VARCHAR2 DEFAULT NULL, p_start_index IN INT DEFAULT 0, p_records_to_take IN INT DEFAULT 0, cp_result OUT SYS_REFCURSOR )
I'm creating all my parameters like this (won't bother to put them all here because there are so many, but I define name, value, type, and direction for all)
OracleParameter paramContracts = new OracleParameter("p_contracts_list", OracleDbType.VarChar, contracts.ToString(), System.Data.ParameterDirection.Input);
The cursor parameter is defined like this:
OracleParameter paramOutputCursor = new OracleParameter("cp_cresult", OracleDbType.Cursor, System.Data.ParameterDirection.Output);
Then I call it like this:
string sqlQuery = "BEGIN OMNIDB.GP_PARTICIPANTDETAILS(:p_contracts_list, :p_participant_type, :p_dob_range_begin, :p_dob_range_end, :p_part_name, :p_ssn, :p_status_list, :p_start_index, :p_records_to_take, :cp_result); END;"; var details = context.Database.SqlQuery<ParticipantDetail>(sqlQuery, paramContracts, paramPartType, paramDobBegin, paramDobEnd, paramPartName, paramSSN, paramStatuses, paramStartIndex, paramRecordsToTake, paramOutputCursor).ToList();
Everything I try gives the same error. With the
: before the param name, without the
:... I verified the spelling of the params in the code match the declaration of the procedure.
What's wrong here?
We have reproduced the issue and are investigating it. We will notify you about the result. If you have any questions about the Devart products, please contact us via http://www.devart.com/company/contactform.html.