C# retrieving dbms_output Text -

If you frequently work with PL/SQL code from your ODP.NET application you may have encountered code that uses the dbms_output package. This is a common debugging technique employed in PL/SQL code. In fact, the Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) has this to say about the dbms_output package:

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

In practice, calls to dbms_output are embedded in the PL/SQL code and then the resulting text is often displayed in a character-based tool such as SQL*Plus. But what if you are using an ODP.NET application? How is it possible to retrieve values into your client application that have been output by the dbms_output package into a server-side buffer?

Fortunately the dbms_output package provides two procedures that can be used to accomplish this:

- get_line
- get_lines

As their names suggest, get_line is used to retrieve a single line from the buffer while get_lines can be used to retrieve multiple lines from the buffer.

In this tip I show you how to use both of these procedures in a single sample application.

In order to retrieve a single line of text, you use a parameter object that represents a Varchar2 data type and a parameter object that represents a Decimal data type. The Varchar2 parameter holds the actual line of text while the Decimal parameter holds the status code returned from the dbms_output package.

The code to retrieve multiple lines of text also uses two OracleParameter objects: one parameter object is a PL/SQL Associative Array to hold the lines of text and the other is a Decimal parameter that holds the number of lines to fetch before the call to dbms_output or the number of lines that were fetched after the call to dbms_output.

Be sure to review the documentation for dbms_output linked above.

Begin by creating the PL/SQL code as the database user that will connect to the database from the ODP.NET application. Of course, this is just sample code to illustrate this technique. Your real code would perform additional activities and almost certainly would be more complex.

The PL/SQL code for the sample package and package body:

create or replace package dbms_output_test as

procedure emit_single_line;

procedure emit_multiple_lines;

end;

create or replace package body dbms_output_test as

procedure emit_single_line is

begin

/* enable dbms_output using defaults */

dbms_output.enable;

/* output a single line of text */

dbms_output.put_line('This is a simple line of text emitted by dbms_output.');

end;

procedure emit_multiple_lines is

begin

/* enable dbms_output using defaults */

dbms_output.enable;

/* output multiple lines of text */

dbms_output.put_line('Sgt. Pepper''s Lonely Hearts Club Band Track List');

dbms_output.put_line('================================================');

dbms_output.put_line('01 - Sgt. Pepper''s Lonely Hearts Club Band');

dbms_output.put_line('02 - With a Little Help From My Friends');

dbms_output.put_line('03 - Lucy in the Sky With Diamonds');

dbms_output.put_line('04 - Getting Better');

dbms_output.put_line('05 - Fixing a Hole');

dbms_output.put_line('06 - She''s Leaving Home');

dbms_output.put_line('07 - Being for the Benefit of Mr. Kite!');

dbms_output.put_line('08 - Within You Without You');

dbms_output.put_line('09 - When I''m Sixty-Four');

dbms_output.put_line('10 - Lovely Rita');

dbms_output.put_line('11 - Good Morning Good Morning');

dbms_output.put_line('12 - Sgt. Pepper''s Lonely Hearts Club Band (Reprise)');

dbms_output.put_line('13 - A Day in the Life');

end;

As you can see, this code simply outputs text using the dbms_output package and performs no other processing. The emit_single_line procedure outputs a single line of sample text while the emit_multiple_lines procedure outputs the track listing for Sgt. Pepper's Lonely Hearts Club Band by The Beatles.

The C# sample code is fairly well commented and should be easy to follow:

using System;

using System.Data;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

namespace DbmsOutput

{

class Program

{

// connect to default database using o/s authenticated account

// be sure to adjust for your environment

private const string constr = "user id=/; enlist=false; pooling=false";

static void Main(string[] args)

{

// create and open connection

OracleConnection con = new OracleConnection(constr);

con.Open();

// call method for single line output

GetSingleLine(con);

// call method for multiple line output

GetMultipleLines(con);

// clean up

con.Dispose();

// prevent console from closing automatically when run from within VS

Console.Write("ENTER to continue...");

Console.ReadLine();

}

static void GetSingleLine(OracleConnection con)

{

// call the procedure that emits a single line of text

string stored_procedure = "dbms_output_test.emit_single_line";

// anonymous pl/sql block to get the line of text

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

// create command and execute the stored procedure

OracleCommand cmd = con.CreateCommand();

cmd.CommandText = stored_procedure;

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

// create parameters for the anonymous pl/sql block

OracleParameter p_line = new OracleParameter("",

OracleDbType.Varchar2,

32000,

"",

ParameterDirection.Output);

OracleParameter p_status = new OracleParameter("",

OracleDbType.Decimal,

ParameterDirection.Output);

// set command text and parameters to get the text output

// and execute the anonymous pl/sql block

cmd.CommandText = anonymous_block;

cmd.CommandType = CommandType.Text;

cmd.Parameters.Add(p_line);

cmd.Parameters.Add(p_status);

cmd.ExecuteNonQuery();

// write location, return status value, and the text to the console window

Console.WriteLine("In method GetSingleLine...");

Console.WriteLine("Return status: {0}", p_status.Value.ToString());

Console.WriteLine("Return text: {0}", p_line.Value.ToString());

Console.WriteLine();

// clean up

p_line.Dispose();

p_status.Dispose();

cmd.Dispose();

}

static void GetMultipleLines(OracleConnection con)

{

// write location to console window

Console.WriteLine("In method GetMultipleLines...");

Console.WriteLine();

// call the procedure that emits multiple lines of text

string stored_procedure = "dbms_output_test.emit_multiple_lines";

// anonymous pl/sql block to get multiples lines of text per fetch

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

// used to indicate number of lines to get during each fetch

const int NUM_TO_FETCH = 8;

// used to determine number of rows fetched in anonymous pl/sql block

int numLinesFetched = 0;

// simple loop counter used below

int i = 0;

// create command and execute the stored procedure

OracleCommand cmd = con.CreateCommand();

cmd.CommandText = stored_procedure;

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

// create parameter objects for the anonymous pl/sql block

OracleParameter p_lines = new OracleParameter("",

OracleDbType.Varchar2,

NUM_TO_FETCH,

"",

ParameterDirection.Output);

p_lines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

p_lines.ArrayBindSize = new int[NUM_TO_FETCH];

// set the bind size value for each element

for (i = 0; i < NUM_TO_FETCH; i++)

{

p_lines.ArrayBindSize[i] = 32000;

}

// this is an input output parameter...

// on input it holds the number of lines requested to be fetched from the buffer

// on output it holds the number of lines actually fetched from the buffer

OracleParameter p_numlines = new OracleParameter("",

OracleDbType.Decimal,

"",

ParameterDirection.InputOutput);

// set the number of lines to fetch

p_numlines.Value = NUM_TO_FETCH;

// set up command object and execute anonymous pl/sql block

cmd.CommandText = anonymous_block;

cmd.CommandType = CommandType.Text;

cmd.Parameters.Add(p_lines);

cmd.Parameters.Add(p_numlines);

cmd.ExecuteNonQuery();

// get the number of lines that were fetched (0 = no more lines in buffer)

numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();

// as long as lines were fetched from the buffer...

while (numLinesFetched > 0)

{

// write the text returned for each element in the pl/sql

// associative array to the console window

for (i = 0; i < numLinesFetched; i++)

{

Console.WriteLine((p_lines.Value as OracleString[])[i]);

}

// re-execute the command to fetch more lines (if any remain)

cmd.ExecuteNonQuery();

// get the number of lines that were fetched (0 = no more lines in buffer)

numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();

}

// just a couple of separator lines

Console.WriteLine();

// clean up

p_numlines.Dispose();

p_lines.Dispose();

cmd.Dispose();

}

Executing the sample produces the following output in the console window:

In method GetSingleLine...

Return status: 0

Return text: This is a simple line of text emitted by dbms_output.

In method GetMultipleLines...

Sgt. Pepper's Lonely Hearts Club Band Track List

================================================

01 - Sgt. Pepper's Lonely Hearts Club Band

02 - With a Little Help From My Friends

03 - Lucy in the Sky With Diamonds

04 - Getting Better

05 - Fixing a Hole

06 - She's Leaving Home

07 - Being for the Benefit of Mr. Kite!

08 - Within You Without You

09 - When I'm Sixty-Four

10 - Lovely Rita

11 - Good Morning Good Morning

12 - Sgt. Pepper's Lonely Hearts Club Band (Reprise)

13 - A Day in the Life

ENTER to continue...

Happy coding...

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论