在VB.NET中使用dbms_output.get_line [英] Using dbms_output.get_line in VB.NET
本文介绍了在VB.NET中使用dbms_output.get_line的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一些存储过程可以执行,这些存储过程使用dbms_output.put_line()
将数据放入输出缓冲区.
I have some stored procedures to execute that use dbms_output.put_line()
to put data into the output buffer.
我知道我需要使用dbms_output.get_line(:line, :status)
来检索该输出.
我正在使用System.Data.OracleClient
来避免Oracle部署的麻烦.
I know I need to use dbms_output.get_line(:line, :status)
to retrieve that output.
I'm using System.Data.OracleClient
to avoid headaches with Oracle deployment.
那么我下面的代码在做什么?
So what am I doing wrong with the code below?
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
推荐答案
Here's how I got it to work: (found it here)
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
这篇关于在VB.NET中使用dbms_output.get_line的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文