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;
}
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.
And the output result is: