ExecuteArray and getting out parameter values

ExecuteArray and getting out parameter values

Postby Partizan » Tue 03 Apr 2012 15:07

Hi, guys.

I'm using Array Binding and passing parameters to a stored procedure which has an out parameter. But I always get "ORA-03113: end-of-file on communication channel" when arrays length is more than one.

How do I get an array of out results?

Thanks in advance.
Alexander Efimov.
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Postby Pinturiccio » Thu 05 Apr 2012 12:37

I'm posting a small sample below, where the ExecuteArray method executes the procedure with the output parameter and the array length equals 2:

The DDL script of the stored procedure
Code: Select all
CREATE OR REPLACE procedure SCOTT.myproc(myin in varchar2, myout out varchar2)
  AS
  BEGIN
    myout := myin;
    END;

C# code:
Code: Select all
static void Main(string[] args)
{
    OracleConnection conn = new OracleConnection("host=orcl1120; user id=scott;password=tiger");
    conn.Open();
    OracleParameter param = new OracleParameter();
    param.ParameterName = "myout";
    param.OracleDbType = OracleDbType.VarChar;
    param.Direction = System.Data.ParameterDirection.Output;
    OracleCommand comm = new OracleCommand("myproc", conn);
    comm.CommandType = System.Data.CommandType.StoredProcedure;
    comm.Parameters.Add("myin", OracleDbType.VarChar);
    comm.Parameters["myin"].Value = new string[2] { "Hello!", "Hi" };
    comm.Parameters.Add(param);
    comm.Parameters["myout"].Value = new string[2];
    comm.ExecuteArray(2);
    string[] str = (string[])param.Value;
    for (int i = 0; i < str.Length; i++)
        Console.WriteLine(str[i]);
    conn.Close();
}
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Postby Partizan » Fri 06 Apr 2012 14:42

Hi. I'm still reproducing the problem with Devart 6.80 trial.

Here's the DDL I have:

Code: Select all
create or replace
PROCEDURE SUBMIT_REQUEST
(
  p_requester_id IN NUMBER
, p_comments IN VARCHAR2
, p_id OUT NUMBER
) AS
BEGIN
  INSERT INTO RSET(REQ_ID, COMMENTS)
  VALUES (p_requester_id, p_comments)
  RETURNING ID INTO p_id;
  COMMIT;
END SUBMIT;


And the code to invoke it:
Code: Select all
         var connection = (OracleConnection)((EntityConnection)ObjectContext.Connection).StoreConnection;
         connection.Open();
         var outParam = new OracleParameter();
         outParam.Direction = ParameterDirection.Output;
         outParam.OracleDbType = OracleDbType.Number;
         outParam.ParameterName = "p_id";

         var command = new OracleCommand("TEST_SCHEMA.SUBMIT_REQUEST", connection);
         command.CommandType = CommandType.StoredProcedure;

         command.Parameters.Add("p_requester_id", OracleDbType.Number);
         command.Parameters["p_request_id"].Value = new decimal[2] {0, 0};

         command.Parameters.Add("p_comments", OracleDbType.VarChar);
         command.Parameters["p_comments"].Value = new string[2] { "", "" };

         command.Parameters.Add(outParam);
         command.Parameters["p_id"].Value = new decimal[2];
         command.ExecuteArray(2);
         connection.Close();
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Postby Pinturiccio » Mon 09 Apr 2012 12:24

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Postby Partizan » Wed 25 Apr 2012 10:47

Guys, any update on this issue?
Partizan
 
Posts: 36
Joined: Fri 13 Nov 2009 10:18

Re: ExecuteArray and getting out parameter values

Postby Shalex » Fri 27 Apr 2012 15:13

We have added the support for executing PL/SQL blocks using the OracleCommand.ExecuteArray method in the Direct mode. We will notify you when the corresponding build of dotConnect for Oracle is available for download.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Re: ExecuteArray and getting out parameter values

Postby Pinturiccio » Wed 23 May 2012 14:35

The new build of dotConnect for Oracle 7.0 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=24180
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle