0

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?

Community
  • 1
  • 1
jeff.eynon
  • 1,296
  • 3
  • 11
  • 29

1 Answers1

0

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.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Please correct the name of parameter in your code: cp_cresult -> cp_result. This should fix the issue. – Devart Sep 15 '15 at 16:07