Getting back DBMS_OUTPUT.PUT_LINE messages

[mbethi]

Hello,
I am executing a procedure with has dbms_output.put_line("diff messages.."); statements. I want to get these messages once execution my procedure completes.

Code:
......
cmd.CommandType = CommandType.StoredProcedure;

var pack = new OraclePackage { Connection = conn, PackageName = "pack name" };

var parms = pack.DescribeProcedure("Procedure name");

pack.Parameters.Add("param", OracleDbType, param, ParameterDirection.Input);

pack.ExecuteProcedure("Procedure name", parms);
....

I want to get the dbms_output.Put_line messages which are executed as part of my procedure. How can i do that?.

Any help is appreciated.

Thank you,
mbethi

[mbethi]

Changed procedure to return the sys_refcursor with the messages i want.

[Pinturiccio]

After calling the procedure, you can execute the following code:

Code: Select all

string anonymous_block = "begin dbms_output.get_lines(:pOutput, :pOutputLen); end;";
int SERVEROUTPUT_MAXLEN = 1024;

OracleCommand comm = conn.CreateCommand();
comm.CommandText = anonymous_block;

OracleParameter pOutput = new OracleParameter("pOutput", OracleDbType.VarChar, ParameterDirection.Output);
pOutput.ArrayLength = SERVEROUTPUT_MAXLEN;
OracleParameter pOutputLen = new OracleParameter("pOutputLen", OracleDbType.Number, 
ParameterDirection.InputOutput);
pOutputLen.Value = SERVEROUTPUT_MAXLEN;

comm.Parameters.Add(pOutput);
comm.Parameters.Add(pOutputLen);

comm.ExecuteNonQuery();

string[] outContent = (string[])pOutput.Value;
foreach (string str in outContent)
    Console.WriteLine(str);

You can also modify your procedure by calling the dbms_output.get_line function after calling dbms_output.put_line in your code. For every call of the dbms_output.get_line function you will have to add an out parameter of the VARCHAR2 type.

For more information about the dbms_output.get_line and dbms_output.get_lines functions, please refer to http://docs.oracle.com/cd/B19306_01/app ... m#BABGBACJ