get the contents of dbms_output after running procedure?

kevinherring · Post by **kevinherring** » Mon 23 Oct 2006 09:37

Hi

I would like return back to my application whatever was printed out by the last run stored procedure.
I have one command object for running my stored procedure and then I have created another one to retrieve the contents of dbms output as I found on another post on the ODAC forum. Both command objects are using the same connection.

Code: Select all

Dim cmdDBMS As New OracleCommand
Dim intStatus As Integer = 0
Dim strBuilder As New System.Text.StringBuilder

With cmdDBMS
            .CommandType = CommandType.StoredProcedure
            .CommandText = "DBMS_OUTPUT.GET_LINE"
            .Connection = cmdDoEstimation.Connection
            .Parameters.Add("line", OracleDbType.VarChar).Direction = ParameterDirection.Output
            .Parameters.Add("status", OracleDbType.Number).Direction = ParameterDirection.Output
End With

While intStatus = 0
    cmdDBMS.ExecuteNonQuery()
    strBuilder.Append(cmdDBMS.Parameters("line").Value)
    intStatus = cmdDBMS.Parameters("status").Value
End While

However, the "line" parameter returns dbnull and the "status" on returns 1.

Any ideas?

kevinherring · Post by **kevinherring** » Mon 23 Oct 2006 10:45

righty, after a bit of investigation I found a solution.
I have take tom kytes code from here:
http://asktom.oracle.com/pls/ask/f?p=49 ... 9812348065
and converted it to vb .net using the corelab driver. I have tried to keep it as true to the original as possible. Perhaps this is something that can be included in the corelab drivers?

Code: Select all

Imports CoreLab.Oracle
Public Class DBMSOutput
    '
    ' our instance variables. It is always best to 
    ' use callable or prepared statements and prepare (parse)
    ' them once per program execution, rather then one per 
    ' execution in the program.  The cost of reparsing is 
    ' very high.  Also -- make sure to use BIND VARIABLES!
    '
    ' we use three statments in this class. One to enable 
    ' dbms_output - equivalent to SET SERVEROUTPUT on in SQL*PLUS.
    ' another to disable it -- like SET SERVEROUTPUT OFF.
    ' the last is to "dump" or display the results from dbms_output
    ' using system.out
    ' 
    ' 
    Private cmdEnable As OracleCommand
    Private cmdDisable As OracleCommand
    Private cmdShow As OracleCommand

    ' 
    ' our constructor simply prepares the three
    ' statements we plan on executing. 
    '
    ' the statement we prepare for SHOW is a block of 
    ' code to return a String of dbms_output output.  Normally, 
    ' you might bind to a PLSQL table type but the jdbc drivers
    ' don't support PLSQL table types -- hence we get the output
    ' and concatenate it into a string.  We will retrieve at least
    ' one line of output -- so we may exceed your MAXBYTES parameter
    ' below. If you set MAXBYTES to 10 and the first line is 100 
    ' bytes long, you will get the 100 bytes.  MAXBYTES will stop us
    ' from getting yet another line but it will not chunk up a line.
    '
    '
    Public Sub New(ByVal conn As OracleConnection)
        cmdEnable = New OracleCommand("begin dbms_output.enable(:1); end;", conn)
        cmdEnable.Parameters.Add("1", OracleDbType.Number)

        cmdDisable = New OracleCommand("begin dbms_output.disable; end;", conn)

        cmdShow = 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;", conn)
        cmdShow.Parameters.Add("maxbytes", OracleDbType.Integer).Value = 32000
        cmdShow.Parameters.Add("done", OracleDbType.Integer).Direction = ParameterDirection.Output
        cmdShow.Parameters.Add("buffer", OracleDbType.VarChar).Direction = ParameterDirection.Output
    End Sub

    '
    ' enable simply sets your size and executes
    ' the dbms_output.enable call
    '
    '
    Public Sub Enable(ByVal size As Integer)
        cmdEnable.Parameters("1").Value = size
        cmdEnable.ExecuteNonQuery()
    End Sub


    '
    ' disable only has to execute the dbms_output.disable call
    '
    Public Sub Disable()
        cmdDisable.ExecuteNonQuery()
    End Sub

    '
    ' show does most of the work.  It loops over
    ' all of the dbms_output data, fetching it in this
    ' case 32,000 bytes at a time (give or take 255 bytes).
    ' It will print this output on stdout by default (just
    ' reset what System.out is to change or redirect this 
    ' output).
    '
    Public Sub Show()
        Dim done As Integer = 0

        While True
            cmdShow.ExecuteNonQuery()
            Console.WriteLine(cmdShow.Parameters("buffer").Value)
            If cmdShow.Parameters("done").Value = 1 Then Exit While
        End While

    End Sub

End Class

it might be better to return the output as a string but I'm sure people can modify it as required. For its usage, check out the tom kyte post.

Alexey · Post by **Alexey** » Tue 24 Oct 2006 06:31

What VB code you'll use is completely up to you. To read from a buffer you should put there something first with PUT_LINE() function.

kevinherring · Post by **kevinherring** » Tue 24 Oct 2006 08:08

eh? did you not read my solution?

Alexey · Post by **Alexey** » Tue 24 Oct 2006 08:29

I did read both your posts and have nothing to add to my answer.
If you have a problem/remark/request, feel free to write it here.