How to return an Oracle Resultset to a DataTable in .NET

A stored Procedure in SQL Server can return a resultset and fill a DataTable simply if we write a SELECT statement within the stored procedure. But how will you return a result set from an Oracle stored procedure.

Cursors in Oracle provide a mechanism by which you can iterate over the records in a database. Using a REF_CURSOR, you can return a resultset from an Oracle stored procedure.

REF CURSOR is a pointer into a result set returned by a PL/SQL query. Unlike a normal cursor, a REF CURSOR is a variable, a reference to a cursor that can be set to point to different result sets at execution time. You can use a REF CURSOR output parameter to pass a result set from an Oracle structured program back to the .NET calling application.

You need to define an output parameter of data type OracleType.Cursor in the calling .NET application to access the result set pointed to by the REF CURSOR. The OracleConnection object in .NET must remain open while the REF CURSOR is being used.

There are two types of ref cursors in Oracle. They are Strong Ref Cursor and Weak Ref Cursor.

For the strong ref cursor, the returning columns with data type and length need to be known at compile time. Example

TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE

For the weak ref cursor, the structure does not need to be known at compile time. The weak ref cursor is also called a dynamic cursor. Example

TYPE WEAK_REF_CURSOR IS REF CURSOR;

To return a resultset from an oracle stored procedure, first you need to create a package in the Oracle database.

Please visit my article in dotnetspider.com to read more on this.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: