Return a variable value and output from PL/SQL code block to C#

Question

I have a code block and I am assigning values to a variable to keep track what is going on while execution of the block.

begin 
 declare 
   stage number := 0; 
   begin
    stage := 1;
    INSERT INTO Country ( code, name) VALUES (1 , 'xxxx');
    stage := 2;
    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; 

I want to ask you how can I get stage value and the value inside DBMS_OUTPUT.PUT_LINE() using C#. I know that this has been already answered but unfortunately I failed finding it.

Answer 1

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;

    OracleParameter stage= new OracleParameter("stage", OracleType.Int16);
    p_country_name.Direction = ParameterDirection.Output;    

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

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

Answer 2

I doubt C# will be able to read dbms_output buffer.

You can however write a stored plsql procedure for this code, and pass out parameter from that procedure to C#