正在从C#中的DBMS_output.GET_LINES获取输出缓冲区

Question

我正试图通过C#从匿名PL/SQL块中的DBMS_OUTPUT.PUT_LINE()方法获得输出。我已经看了其他几个相关的问题，但仍然有问题。执行匿名块的返回代码是返回-1，根据文档应该是正确的。

我将DBMS_OUTPUT.ENABLE()设置为NULL是为了不设置特定的缓冲区大小，然后使用DBMS_OUTPUT.GET_LINES()方法从该缓冲区中获取行。

它在缓冲区中不返回任何内容(空的OracleString[](，并返回0行。我的匿名PL/SQL块很简单，但应该适用于任何程序。

DECLARE
lvsName VARCHAR2(6) := 'Oracle';
BEGIN
DBMS_OUTPUT.PUT_LINE('Do you see me?');
DBMS_OUTPUT.PUT_LINE('My name is: ' || lvsName);    
END;

我错过了什么？

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;
}

Answer 1

代码的主要问题是没有为输出缓冲区的每个元素设置绑定大小。此外，在检索结果时，它没有正确地索引输出缓冲区。最后，执行顺序也起到了一定的作用：在执行匿名代码块之前，必须首先启用输出。所做的每一项更改都会在以下MCVE中进行评论。只进行了必要的更改以使其正常工作。

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 += "rn";
}
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();
}

输出结果为：

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

Answer 2

感谢jsanalytics的回答，它为解决方案提供了良好的基础。不过，上述解决方案存在一些问题，主要与sql的使用有关。长度在许多地方是没有意义的。这里有一个可重复使用的解决方案，它纠正了一些问题。

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;
}
}
}
}

Answer 3

在我看来，你做这件事的顺序不对。。。

// 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;";

在设置(启用(DBMS_OUTPUT和使用GET_LINES获得输出之间，应该是您的写命令，但它是您执行的第一件事。

试着改变顺序。让我知道它是否有效，因为我没有尝试过(我不习惯C#……我在Java中有它(。

Answer 4

我不会说C#，但在您的代码中，我看不到您为numLines变量赋值的地方。

DBMS_OUTPUT.GET_LINES (
lines       OUT     CHARARR,
numlines    IN OUT  INTEGER);

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;