4

I know how to get output from DBMS_OUTPUT.GET_LINE().

You can refer http://oradim.blogspot.com.tr/2007/05/odpnet-tip-retrieving-dbmsoutput-text.html

On this page getting output form DBMS_OUTPUT.GET_LINES() method is also explained by using ODP.Net. Is there any way to manage this using only ADO.NET.

For example, how can I read all outputs from below

begin 
 declare 
   stage number := 0; 
   begin
    DBMS_OUTPUT.PUT_LINE('STARTING:'); 
    INSERT INTO Country ( code, name) VALUES (1 , 'xxxx');
    INSERT INTO City ( code, name) VALUES (1 , 'yyyy');
    DBMS_OUTPUT.PUT_LINE('DONE:'); 
COMMIT; 

EXCEPTION  -- exception handlers begin 
  WHEN OTHERS THEN  -- handles all other errors 
   DBMS_OUTPUT.PUT_LINE('Error occured, rollback...');  
   DBMS_OUTPUT.get_LINE(:1, :2);
   stage := -1;
   ROLLBACK; 
  end;
end; 

Output should be like this:

STARTING
DONE

I have this code block, but it returns only first output line

using (OracleCommand cmd = cnn.CreateCommand())
{
    OracleParameter status = new OracleParameter(":1", OracleType.VarChar, 32000);
    p_line.Direction = ParameterDirection.Output;

    OracleParameter line = new OracleParameter(":2", OracleType.Double);
    p_status.Direction = ParameterDirection.Output;  

    cmd.CommandText = script;
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(status);
    cmd.Parameters.Add(line );
    cmd.ExecuteNonQuery();

    string status = status.Value.ToString();
    string line = line.Value.ToString();
}

Output:

STARTING

1 Answer 1

1

DBMS_OUTPUT.GET_LINE returns only the first line of the buffer (and removes it). If you use this method you need to invoke it once for each line. If there is no line available the returned status is 1.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.