Getting output buffer from DBMS_OUTPUT.GET_LINES in C#

The main problem with your code was that it was not setting the bind size for each element of your output buffer. Also it was not properly indexing the output buffer when retrieving the results. And finally, the order of execution also plays a role: you have to first enable your output before executing your anonymous block of code. Every single change made is commented in the following MCVE. Only necessary changes to get it working were made.

static void Main(string[] args){    string str = "User Id=xxx; password=xxx; Data Source=localhost:1521/xxx;";    string sql = @"DECLARE lvsName VARCHAR2(6) := 'Oracle'; BEGIN  DBMS_OUTPUT.PUT_LINE('Do you see me?'); DBMS_OUTPUT.PUT_LINE('My name is: ' || lvsName); END;";    OracleConnection _connection = new OracleConnection(str);    try    {        _connection.Open();        //adapter not being used        //using (OracleDataAdapter oda = new OracleDataAdapter())        using (OracleCommand cmd = new OracleCommand(sql, _connection))        {            // First enable buffer output            // Set output Buffer            cmd.CommandText = "BEGIN DBMS_OUTPUT.ENABLE(NULL); END;";            cmd.CommandType = CommandType.Text;            cmd.ExecuteNonQuery();            // Then execute anonymous block            // Execute anonymous PL/SQL block            cmd.CommandText = sql;            cmd.CommandType = CommandType.Text;            var res = cmd.ExecuteNonQuery();            // Get output            cmd.CommandText = "BEGIN DBMS_OUTPUT.GET_LINES(:outString, :numLines); END;";            cmd.CommandType = CommandType.Text;            cmd.Parameters.Clear();            cmd.Parameters.Add(new OracleParameter("outString", OracleDbType.Varchar2, int.MaxValue, ParameterDirection.Output));            cmd.Parameters["outString"].CollectionType = OracleCollectionType.PLSQLAssociativeArray;            cmd.Parameters["outString"].Size = sql.Length;            cmd.Parameters["outString"].ArrayBindSize = new int[sql.Length];            // set bind size for each array element            for (int i = 0; i < sql.Length; i++)            {                cmd.Parameters["outString"].ArrayBindSize[i] = 32000;            }            cmd.Parameters.Add(new OracleParameter("numLines", OracleDbType.Int32, ParameterDirection.InputOutput));            cmd.Parameters["numLines"].Value = 10; // Get 10 lines            cmd.ExecuteNonQuery();            int numLines = Convert.ToInt32(cmd.Parameters["numLines"].Value.ToString());            string outString = string.Empty;            // Try to get more lines until there are zero left            while (numLines > 0)            {                for (int i = 0; i < numLines; i++)                {                    // use proper indexing here                    //OracleString s = (OracleString)cmd.Parameters["outString"].Value;                    OracleString s = ((OracleString[])cmd.Parameters["outString"].Value)[i];                    outString += s.ToString();                    // add new line just for formatting                    outString += "\r\n";                }                cmd.ExecuteNonQuery();                numLines = Convert.ToInt32(cmd.Parameters["numLines"].Value.ToString());            }            Console.WriteLine(outString);        }    }    catch (Exception ex)    {        Console.WriteLine(ex.ToString());    }    _connection.Close();    _connection.Dispose();    Console.WriteLine("Press RETURN to exit.");    Console.ReadLine();}

And the output result is:

Do you see me?My name is: OraclePress RETURN to exit.

Thanks for the answer above by jsanalytics which provided a good basis for a solution. There are some issues with the above solution though, mostly having to do with the usage of sql.Length in many places where it doesn't make sense. Here is a reusable solution which corrects some of the issues.

using Oracle.DataAccess.Client;using Oracle.DataAccess.Types;using System;using System.Collections.Generic;using System.Data;using System.Linq;namespace MyNamespace{    public static class DbmsOutputHelper    {        public const int DefaultReadBatchSize = 10;        public static void EnableDbmsOutput(this OracleConnection conn)        {            using (var cmd = conn.CreateCommand())            {                cmd.CommandText = "DBMS_OUTPUT.ENABLE";                cmd.CommandType = CommandType.StoredProcedure;                cmd.ExecuteNonQuery();            }        }        public static void DisableDbmsOutput(this OracleConnection conn)        {            using (var cmd = conn.CreateCommand())            {                cmd.CommandText = "DBMS_OUTPUT.DISABLE";                cmd.CommandType = CommandType.StoredProcedure;                cmd.ExecuteNonQuery();            }        }        public static List<string> ReadDbmsOutput(this OracleConnection conn, int readBatchSize = DefaultReadBatchSize)        {            if (readBatchSize <= 0)            {                throw new ArgumentOutOfRangeException(nameof(readBatchSize), "must be greater than zero");            }            using (var cmd = conn.CreateCommand())            {                cmd.CommandText = "DBMS_OUTPUT.GET_LINES";                cmd.CommandType = CommandType.StoredProcedure;                var linesParam = cmd.Parameters.Add(new OracleParameter("lines", OracleDbType.Varchar2, int.MaxValue, ParameterDirection.Output));                linesParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;                linesParam.Size = readBatchSize;                linesParam.ArrayBindSize = Enumerable.Repeat(32767, readBatchSize).ToArray();   // set bind size for each array element                var numLinesParam = cmd.Parameters.Add(new OracleParameter("numlines", OracleDbType.Int32, ParameterDirection.InputOutput));                var result = new List<string>();                int numLinesRead;                do                {                    numLinesParam.Value = readBatchSize;                    cmd.ExecuteNonQuery();                    numLinesRead = ((OracleDecimal)numLinesParam.Value).ToInt32();                    var values = (OracleString[])linesParam.Value;                    for (int i = 0; i < numLinesRead; i++)                    {                        result.Add(values[i].ToString());                    }                } while (numLinesRead == readBatchSize);                return result;            }        }    }}

It looks to me that you're doing it in the wrong order...

// Execute anonymous PL/SQL blockcmd.CommandType = CommandType.Text;var res = cmd.ExecuteNonQuery();// Set output Buffercmd.CommandText = "BEGIN DBMS_OUTPUT.ENABLE(NULL); END;";cmd.CommandType = CommandType.Text;cmd.ExecuteNonQuery();// Get outputcmd.CommandText = "BEGIN DBMS_OUTPUT.GET_LINES(:outString, :numLines); END;";

Between setting (enabling) the DBMS_OUTPUT and getting the output using GET_LINES should be your write command, but instead, it's the first thing you're executing.

Try to change the order. Let me know if it works cause I didn't try it (I'm not used to C#... I have it in Java).