Getting back DBMS_OUTPUT.PUT_LINE messages

Getting back DBMS_OUTPUT.PUT_LINE messages

Postby mbethi » Tue 14 May 2013 19:09

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
 
Posts: 3
Joined: Tue 14 May 2013 18:57

Re: Getting back DBMS_OUTPUT.PUT_LINE messages

Postby mbethi » Fri 17 May 2013 18:20

Changed procedure to return the sys_refcursor with the messages i want.
mbethi
 
Posts: 3
Joined: Tue 14 May 2013 18:57

Re: Getting back DBMS_OUTPUT.PUT_LINE messages

Postby Pinturiccio » Mon 20 May 2013 08:24

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/appdev.102/b14258/d_output.htm#BABGBACJ
Pinturiccio
Devart Team
 
Posts: 2045
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle