ExecuteArray and getting out parameter values

[Partizan]

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.

[Pinturiccio]

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();
}

[Partizan]

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();

[Pinturiccio]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Partizan]

Guys, any update on this issue?

[Shalex]

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.

[Pinturiccio]

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