Calling an Oracle stored procedure with Entity Framework code-first and DevArt Drivers

devart entity-framework entity-framework-6 oracle stored-procedures

Question

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?

1
0
5/23/2017 12:03:53 PM

Popular Answer

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.

0
9/14/2015 12:37:44 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow