我正在尝试获取输出DBMS_OUTPUT.PUT_LINE()
通过 C# 在我的匿名 PL/SQL 块中调用方法。我在这里查看了其他几个相关问题,但仍然遇到麻烦。执行匿名块的返回码正在return-1
,这应该是正确的docs https://docs.oracle.com/en/database/oracle/oracle-database/12.2/odpnt/CommandExecuteNonQuery.html#GUID-E1E79471-6037-4490-BDE7-ADE252922C83.
我正在设置DBMS_OUTPUT.ENABLE()
to 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;
}
您的代码的主要问题是它没有为输出缓冲区的每个元素设置绑定大小。此外,在检索结果时,它没有正确索引输出缓冲区。最后,执行顺序也发挥着作用:在执行匿名代码块之前,您必须首先启用输出。所做的每一个更改都会在下面进行注释MCVE https://stackoverflow.com/help/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 += "\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();
}
输出结果为:
Do you see me?
My name is: Oracle
Press RETURN to exit.
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)