Get DBMS.Output.Put_Line

[ronnie]

Hello,

We currently have Oracle stored procedures that return data via DBMS_Outout.Put_Lines.

Is there a way to capture that output using the dotConnect for Oracle components?

Thanks,

Ronnie

[Shalex]

Try this code to retrieve values from a server-side buffer that have been output by the dbms_output package:

Code: Select all

        static void Main(string[] args) {
            OracleConnection conn = new OracleConnection("host=orcl1110;uid=scott;pwd=tiger;");
            conn.Open();
            GetMultipleLines(conn);
        }

        static void GetMultipleLines(OracleConnection con) {
            string stored_procedure = "emit_multiple_lines";

            string anonymous_block = "begin dbms_output.get_lines(:1, :2); end;";

            const int NUM_TO_FETCH = 8;
            int numLinesFetched = 0;

            OracleCommand cmd = con.CreateCommand();
            cmd.CommandText = stored_procedure;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();

            OracleParameter p_lines = new OracleParameter("1", OracleDbType.VarChar);
            p_lines.Direction = ParameterDirection.Output;
            p_lines.ArrayLength = NUM_TO_FETCH;

            OracleParameter p_numlines = new OracleParameter("2", OracleDbType.Number);
            p_numlines.Direction = ParameterDirection.InputOutput;
            p_numlines.Value = NUM_TO_FETCH;

            cmd.CommandText = anonymous_block;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(p_lines);
            cmd.Parameters.Add(p_numlines);
            cmd.ExecuteNonQuery();

            numLinesFetched = Decimal.ToInt32((decimal)p_numlines.Value);

            while (numLinesFetched > 0) {
                String[] str = (string[])p_lines.Value;
                for (int i = 0; i < numLinesFetched; i++) {
                    Console.WriteLine(str[i]);
                }
                cmd.ExecuteNonQuery();
                numLinesFetched = Decimal.ToInt32((decimal)p_numlines.Value);
            }
        }

If you encounter any difficulties, please contact us.