Htykuut

I'm trying to get the output from the DBMS_OUTPUT.PUT_LINE() 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 -1, which should be correct based on the docs.

I'm setting the DBMS_OUTPUT.ENABLE() to NULL in order to not set a specific buffer size, then using the DBMS_OUTPUT.GET_LINES() method in order to get the lines from that buffer.

It returns nothing in the buffer (An empty OracleString) and returns 0 lines. My anonymous PL/SQL block is simple like this, but should work for any.

DECLARE

    lvsName VARCHAR2(6) := 'Oracle';

BEGIN

    DBMS_OUTPUT.PUT_LINE('Do you see me?');

    DBMS_OUTPUT.PUT_LINE('My name is: ' || lvsName);    

END;

What am I missing?

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 > 0)

     {

         for (int i = 0; i < numLines; i++)

         {

             OracleString s = (OracleString)cmd.Parameters["outString"].Value;

             outString += s.ToString();

         }



         cmd.ExecuteNonQuery();

         numLines = Convert.ToInt32(cmd.Parameters["numLines"].Value.ToString());

     }



     return outString;

}

I don't speak C# but i don't see in your code where you are assigning value to the numLines variable.

  DBMS_OUTPUT.GET_LINES (

   lines       OUT     CHARARR,

   numlines    IN OUT  INTEGER);

Example in plsql:

DECLARE

   v_array       DBMS_OUTPUT.CHARARR;

   v_lines   NUMBER;

BEGIN

   DBMS_OUTPUT.PUT_LINE ('aaaaa');

   DBMS_OUTPUT.put_line ('bbbb');

   DBMS_OUTPUT.put_line ('ccccc');

   v_lines := 1000; -- Number of lines you want to retrieve from the buffer.  

   DBMS_OUTPUT.GET_LINES (v_array, v_lines);



   DBMS_OUTPUT.put_line(v_lines); -- Lines retrieved from buffer.

   FOR idx IN nvl(v_array.FIRST,1) .. nvl(v_array.LAST,-1)

   LOOP

      DBMS_OUTPUT.put_line (v_array (idx));

   END LOOP;

END;