ODP.NET

I made some store procedures with the DBMS_OUTPUT instruction. I already can return the value of the DBMS_OUT but only for a single line. I will show you the code

Dim DataSource As String = "Data Source=" + DB + "; User Id=" + UID + ";Password=" + PWD + ";"
        Dim conn As New OracleConnection(DataSource)
        conn.Open()
        Dim cmd As New OracleCommand
        cmd.Connection = conn

        Dim sp_str As String
        Dim block As String

        sp_str= "dbms_output_test.emit_single_line"
        block = " begin dbms_output.get_line(:1,:2); end;"

        cmd.CommandText = sp_str
        cmd.CommandType = CommandType.StoredProcedure

        Dim dr As OracleDataReader = cmd.ExecuteReader
        dr.Read()
     
        Dim p1 As New OracleParameter("1", OracleDbType.Varchar2)
        p1.Direction = ParameterDirection.Output
        p1.Size = 255

        Dim p2 As New OracleParameter("2", OracleDbType.Decimal)
        p2.Direction = ParameterDirection.Output

        cmd.Parameters.Add(p1)
        cmd.Parameters.Add(p2)

        cmd.CommandText = block
        cmd.CommandType = CommandType.Text

        dr.Read()
        LblDBMS.Text = p1.Value.ToString()

        p1.Dispose()
        p2.Dispose()
        cmd.Dispose()
        conn.Close()

But i don't know how do this for multiples lines the code of the SP is the following:

create or replace package body dbms_output_test as
  procedure emit_single_line is
  begin
    /* enable dbms_output using defaults */
    dbms_output.enable;
 
    /* output a single line of text */
    dbms_output.put_line('DBMS Test');
  end;
 

I need to know how to return multiples lines of DBMS_OUTPUT on a textbox for example:

/* output a single line of text */
dbms_output.put_line('1.     DBMS Test');
dbms_output.put_line('2.     DBMS Test');
dbms_output.put_line('3.     DBMS Test');
dbms_output.put_line('4.     DBMS Test');

Thanks, for your help.

Carlos Robles.

Edited by: Carlos Robles on 29-oct-2009 14:12