Call Oracle stored procedures using devart dotConnect

Question

I'm new in Entity Framework and trying to call oracle stored procedures, but without success. So Here is my question:

How to call oracle stored procedures using devart dotConnect?

For example, I have stored procedure:

procedure get_problems(res out sys_refcursor) is
  begin

   open res 
   for
   select id, name
   from problems;  

  end;

And from C# I'm tying to call:

 using (Entities entities = new Entities())
 {
     ObjectParameter res = new ObjectParameter("res", typeof(byte[]));
     ObjectResult<PROBLEM> problems = entities.SelectAllProblems(res);
 }

But it throws "EntityCommandExecutionException":

An error occurred while executing the command definition. See the inner exception for details.

Here is the inner exception:

ORA-06550: line 2, column 3:\nPLS-00306: wrong number or types of arguments in call to 'GET_PROBLEMS'\nORA-06550: line 2, column 3:\nPL/SQL: Statement ignored

I used

"typeof(byte[])"

as ObjectParameter type, because I saw this in Devart Entity Developer's generated code.

p.s. By the way, how will you recommend dotConnect in large projects?

score 0 · Answer 1 · answered May 23 '12 at 09:09

I know this was asked a while ago but it might help someone else as it took me a while to figure this out. Here's how we call a stored proc (SID_PGet) within a package (P_SID) and return a single string value using DotConnect. (This only returns a single value - I'm currently trying to find out how to return a sys_refcursor).

Here's the stored proc:

PROCEDURE SID_PGet(io_SID OUT varchar2) is
Begin
   io_SID:=GetSID; -- GetSID returns a unique varchar value
End;

And in the DbContext in C#:

public string GetNextSId()
{
    var parameter = new Devart.Data.Oracle.OracleParameter("io_SID", Devart.Data.Oracle.OracleDbType.VarChar, ParameterDirection.Output);
    this.Database.ExecuteSqlCommand("BEGIN P_SID.SID_PGet(:io_SID); END;", parameter);
    var sid = parameter.Value as string;

    return sid;
}

score 0 · Accepted Answer · answered Apr 15 '11 at 12:59

0

Take a look at this article in our blog.
You can contact us using our Forums or Feedback Page.

answered Apr 15 '11 at 12:59

Devart

119,203
23
166
186

4

[this article](http://www.devart.com/blogs/dotconnect/index.php/working-with-stored-procedures-and-ref-cursors.html) is unavailable, please update that. thnx – Amir Feb 23 '14 at 16:07
"The resource you are looking for has been removed, had its name changed, or is temporarily unavailable." Please update the link and preferably add relevant code to this answer so future linkrot will not invalidate it. – BCdotWEB Aug 04 '15 at 11:30

Call Oracle stored procedures using devart dotConnect

2 Answers2