How to get the dbms.output value returned by a PL-SQL block in C#

Question

I am trying to execute a PL-SQL block in c# using System.Data.OrcaleClient. The PL-SQL block when executed in oracle, prints the result using dbms.ouput.

I want to get that "dbms.ouput" result in my C# code.

Please help.

Why don't you turn you block into a function and return the result? — Ben
– Ben, Commented Mar 3, 2012 at 10:57
Hi Ben, we are integrating with a third party and they have provided us with the PL-SQL block, to get the required result — Dinu
– Dinu, Commented Mar 3, 2012 at 10:59

user800014 · Accepted Answer · 2012-03-03 12:36:15Z

4

Try to use get_line function of dbms_output package. You can create a procedure to return the output. Something like this (just a example):

procedure call_with_output(p_output out varchar2) is
  vret integer := 0;
  vtxt varchar2(4000);
begin
  dbms_output.enable;
  -- here call code that generate lines
  -- use the loop to retrieve info
  while vret = 0 loop
    dbms_output.get_line(vtxt, vret);
    if vret = 0 then
      if p_output  is null then
        p_output := vtxt;
      else
       p_output := p_output || chr(10) || vtxt;
      end if;
    end if;
  end loop;
  dbms_output.disable;
end;

answered Mar 3, 2012 at 12:36

user800014

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

5 Comments

Luke Woodward Over a year ago

I like your solution. It combines the simplicity of using get_line (instead of get_lines) with a single call to the server. The answer I was working on involved calling get_lines via ODP.NET, which is a ridiculous faff. +1

Justin Cave Over a year ago

+1 Note, however, that this will only work if the total amount of output is 32k or less. That's probably the case but you can get a decent amount of debug output get written to dbms_output if you're generating it in a loop.

Shannon Severance Over a year ago

To get 32K, the declaration of vtxt must be changed to vtxt varcahr2(32767)

Dinu Over a year ago

@Sérgio Michels - Thats a nice solution but the data returned by the pl-sql block is huge.So since there is a size limit in using get_line,I think it wouldnt work :(

user800014 Over a year ago

@Dinu Try modify the example to insert in a temporary table, instead of return as out of the procedure.

SKINDER · Accepted Answer · 2012-09-06 15:33:35Z

I am using the next method:

    private string GetDbmsOutputLine()
    {
        OracleCommand command = new OracleCommand
        {
            Connection = <connection>,
            CommandText = "begin dbms_output.get_line(:line, :status); end;",
            CommandType = CommandType.Text
        };

        OracleParameter lineParameter = new OracleParameter("line",  
            OracleType.VarChar);
        lineParameter.Size = 32000;
        lineParameter.Direction = ParameterDirection.Output;
        command.Parameters.Add(lineParameter);

        OracleParameter statusParameter = new OracleParameter("status",  
            OracleType.Int32);
        statusParameter.Direction = ParameterDirection.Output;
        command.Parameters.Add(statusParameter);

        command.ExecuteNonQuery();

        if (command.Parameters["line"].Value is DBNull)
            return null;

        string line = command.Parameters["line"].Value as string;

        return line;
    }

Call it several times to get multistring value because there are problems with calling dbms_output.get_lines with System.Data.OracleClient.

Collectives™ on Stack Overflow

How to get the dbms.output value returned by a PL-SQL block in C#

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related