Howto retrieve DBMS_OUTPUT.GET_LINES() results ??

[mirkof]

Hi,

I'm trying to find out how to retrieve the result of the DBMS_OUTPUT.GET_LINES() procedure in my C# code. I managed to retrieve a single line using the DBMS_OUTPUT.GET_LINE() procedure.

The problem I have is, that the first parameter of the GET_LINES() procedure is a CHARARR Type and I don't know which OracleDbType I should use.

If you are using .NET Connect from Oracle there you can do something like this (using an associative array parameter):

// create an associative array parameter
OracleParameter p_5 = new OracleParameter();
p_5.OracleDbType = OracleDbType.Varchar2;
p_5.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_5.Direction = ParameterDirection.Output;
p_5.Value = null;
p_5.Size = 2;
p_5.ArrayBindSize = new int[2] { 32, 32 };

Is there a similar way in the dotConnect for Oracle component ?

Thanks for your help,
Mirko

[Shalex]

Here is a small example how you can work with DBMS_OUTPUT.CHARARR:

Code: Select all

DDL:
create or replace PROCEDURE getlines(lines OUT DBMS_OUTPUT.CHARARR)
AS
BEGIN
  lines(1):= 'aaa';
  lines(2):= 'bbb';
END;

C#:
    using (OracleConnection conn = new OracleConnection()) {
        conn.ConnectionString = "server=orcl1120;uid=***;pwd=***;";
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandText = "getlines";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("lines", OracleDbType.VarChar).Direction = ParameterDirection.Output;
        cmd.Parameters["lines"].ArrayLength = 2;
        cmd.ExecuteNonQuery();
        Devart.Data.Oracle.OracleString[] str = (OracleString[])cmd.Parameters["lines"].OracleValue;
        foreach(OracleString line in str)
            Console.WriteLine(line);
    }

For more information about the ArrayLength property, please refer to
http://www.devart.com/dotconnect/oracle ... ength.html.