在 VB.NET 中使用 dbms_output.get_line

Question

我有一些要执行的存储过程，它们使用 dbms_output.put_line() 来将数据放入输出缓冲区。

我知道我需要使用 dbms_output.get_line(:line, :status) 来检索该输出。 我使用 System.Data.OracleClient 来避免 Oracle 部署带来的麻烦。

那么我下面的代码做错了什么？

Dim cmdSproc As OracleCommand = cnOracle.CreateCommand()
Dim strOracle As New OracleString()
Dim opaLine As New OracleParameter("lineOut", OracleType.VarChar, 255)
opaLine.Direction = ParameterDirection.Output
Dim opaStatus As New OracleParameter("status", 0)
cmdSproc.CommandText = "begin dbms_output.get_line(:lineOut,:status); end;"
cmdSproc.Parameters.Add(opaLine)
cmdSproc.Parameters.Add(opaStatus)

Dim strOutput As String = ""
strOracle = "0"
Try
   While strOracle = "0"
      cmdSproc.ExecuteOracleNonQuery(strOracle)
      strOutput = strOutput & strOracle.ToString() & vbNewLine
   End While
Catch ex As Exception
   MsgBox(ex.Message)
End Try

Answer 1

在vb.net中SKINDER朋友的代码格式如下...感谢分享

Sub _showDbms() 
            Dim c As New OracleCommand()
            c.Connection = frmMain._cnn
            With c
                .CommandType = CommandType.Text
                .CommandText = "begin dbms_output.get_line(:line, :status); end;"
                .Parameters.Add(New OracleParameter("line", OracleDbType.Varchar2)).Size = 32000
                .Parameters("line").Direction = ParameterDirection.Output
                .Parameters.Add(New OracleParameter("status", OracleDbType.Int32))
                .Parameters("status").Direction = ParameterDirection.Output
            End With
            c.ExecuteNonQuery()
            If c.Parameters("line").Value IsNot DBNull.Value Then
                MsgBox(c.Parameters("line").Value.ToString)
            End If
    End Sub 

Answer 2

这是我如何让它工作的：（发现它此处）

Dim cmdGetOutput As New OracleCommand("declare " & _
    " l_line varchar2(255); " & _
    " l_done number; " & _
    " l_buffer long; " & _
    "begin " & _
    " loop " & _
    " exit when length(l_buffer)+255 > :maxbytes OR l_done =1; " & _
    " dbms_output.get_line( l_line, l_done ); " & _
    " l_buffer := l_buffer || l_line || chr(10); " & _
    " end loop; " & _
    " :done := l_done; " & _
    " :buffer := l_buffer; " & _
    "end;", cnOracle)
cmdGetOutput.Parameters.Add("maxbytes", OracleType.Int16)
cmdGetOutput.Parameters("maxbytes").Value = 32000
cmdGetOutput.Parameters.Add("done", OracleType.Int16)
cmdGetOutput.Parameters("done").Direction = ParameterDirection.Output
cmdGetOutput.Parameters.Add("buffer", OracleType.LongVarChar, 32000)
cmdGetOutput.Parameters("buffer").Direction = ParameterDirection.Output

Dim strOutput As String = ""
Dim intStatus As Integer = 0
Try
   While True
      cmdGetOutput.ExecuteNonQuery()
      strOutput = strOutput & cmdGetOutput.Parameters("buffer").Value & vbNewLine
      If cmdGetOutput.Parameters("done").Value = 1 Then
         Exit While
      End If
   End While
Catch ex As Exception
   MsgBox(ex.Message)
Finally
   MsgBox(strOutput)
   cnOracle.Close()
End Try

Answer 3

在 C# 中，我使用下一个方法：

    private string GetDbmsOutputLine()
    {
        OracleCommand command = new OracleCommand
        {
            Connection = <connection>,
            CommandText = "begin dbms_output.get_line(:line, :status); end;",
            CommandType = CommandType.Text
        };

        OracleParameter lineParameter = new OracleParameter("line",  
            OracleType.VarChar);
        lineParameter.Size = 32000;
        lineParameter.Direction = ParameterDirection.Output;
        command.Parameters.Add(lineParameter);

        OracleParameter statusParameter = new OracleParameter("status",  
            OracleType.Int32);
        statusParameter.Direction = ParameterDirection.Output;
        command.Parameters.Add(statusParameter);

        command.ExecuteNonQuery();

        if (command.Parameters["line"].Value is DBNull)
            return null;

        string line = command.Parameters["line"].Value as string;

        return line;
    }