title: Getting output buffer from DBMS_OUTPUT.GET_LINES in C#
body: <p>I'm trying to get the output from the <code>DBMS_OUTPUT.PUT_LINE()</code> method in my anonymous PL/SQL block through C#. I've looked at a couple of other related questions here, but am still having trouble. The return code of executing the anonymous block is returning <code>-1</code>, which should be correct based on the <a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/odpnt/CommandExecuteNonQuery.html#GUID-E1E79471-6037-4490-BDE7-ADE252922C83" rel="noreferrer">docs</a>.</p>
  
  <p>I'm setting the <code>DBMS_OUTPUT.ENABLE()</code> to <code>NULL</code> in order to not set a specific buffer size, then using the <code>DBMS_OUTPUT.GET_LINES()</code> method in order to get the lines from that buffer.</p>
  
  <p>It returns nothing in the buffer (An empty <code>OracleString[]</code>) and returns <code>0</code> lines. My anonymous PL/SQL block is simple like this, but should work for any.</p>
  
  <pre><code>DECLARE
      lvsName VARCHAR2(6) := 'Oracle';
  BEGIN
      DBMS_OUTPUT.PUT_LINE('Do you see me?');
      DBMS_OUTPUT.PUT_LINE('My name is: ' || lvsName);    
  END;
  </code></pre>
  
  <p>What am I missing?</p>
  
  <pre><code>using (OracleDataAdapter oda = new OracleDataAdapter())
  using (OracleCommand cmd = new OracleCommand(sql, _connection))
  {
      // Execute anonymous PL/SQL block
      cmd.CommandType = CommandType.Text;
      var res = cmd.ExecuteNonQuery();
  
      // Set output Buffer
      cmd.CommandText = "BEGIN DBMS_OUTPUT.ENABLE(NULL); END;";
      cmd.CommandType = CommandType.Text;
      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];
      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 &gt; 0)
       {
           for (int i = 0; i &lt; numLines; i++)
           {
               OracleString s = (OracleString)cmd.Parameters["outString"].Value;
               outString += s.ToString();
           }
  
           cmd.ExecuteNonQuery();
           numLines = Convert.ToInt32(cmd.Parameters["numLines"].Value.ToString());
       }
  
       return outString;
  }
  </code></pre>