Home > .net 2.0, .net 3.5, Microsoft, Oracle, SQL, Visual Studio 2005 > Enterprise Library and Oracle Stored Procedure Record Sets

Enterprise Library and Oracle Stored Procedure Record Sets

August 27, 2007

Perhaps I just don’t sacrifice enough innocents to Oracle daily to keep up on the odd things regarding Enterprise Library and Oracle.  Or, I’m just very lucky to have most of my projects based on Microsoft SQL Server and just expect things to work just as easily with Oracle.

So what about returning a record set in Oracle?  It requires an output cursor.  Unlike tSQL, you can’t simply write a SQL command that “SELECT * FROM Customers” and expect a dataset to be returned.  You have to associate it to a cursor and then use the Oracle type called “ref cursor” to access it.

If I was not using Enterprise Library, I could use an Oracle Connection or ODP.NET to access the OracleType.Cursor.  Unfortunately, for now at least, I want to use the default OracleClient and I want to avoid hacking the Enterprise Library (Ref #1).

On our Oracle side, let’s create a simple aggregation query that I was working on earlier that accepts three input variables: quarter, year (stored as a string ), and the identifier for the teacher.  It also has one output variable, which we’ll call resultset_out of type Types.cursorType (Ref #2).

CREATE OR REPLACE PROCEDURE GetMathData
( quarter_in IN NUMBER,
  year_in IN VARCHAR,
  sin_in IN NUMBER,
  resultset_out out TYPES.cursorType)  AS BEGIN

OPEN resultset_out FOR
SELECT
    pupil_id as StudentId,
    teacher_id as TeacherId,
    report_id,
    nvl((select ‘true’ from dual where
      question_val(pupil_id, 143, r.quarter, r.year) = ‘true’ and 
      question_val(pupil_id, 152, r.quarter, r.year) = ‘true’), ‘false’) as Rep10,
    question_val(pupil_id, 94, r.quarter, r.year) as PatternAB,
    question_val(pupil_id, 95, r.quarter, r.year) as PatternABC,
    question_val(pupil_id, 96, r.quarter, r.year) as PatternAAB,
    question_val(pupil_id, 97, r.quarter, r.year) as PatternAABB
  FROM Reports r
  WHERE r.TEACHER_ID = sin_in
    AND r.YEAR = year_in
    AND r.QUARTER = quarter_in;

END GetMathData;

From first glance, that looks fine, but there’s nothing returned.  To access our result set, we have to echo it back out using PRINT.

variable resultSet refcursor;
exec GETCIPMATHDATA(4, ‘2006-2007′, [somenumber], :resultSet);
print :resultSet;

Now that we have that taken care of, let’s use it in Enterprise Library.  Be sure to import .Data and .Common.

Database db =

DatabaseFactory.CreateDatabase(“OraDatabase”);

 

DbCommand cmd =

db.GetStoredProcCommand(“GetMathData”,

4,

“2006-2007″,

[somenumber]);

 

DataTable dt =

db.ExecuteDataSet(cmd).Tables[0];

 

GridView gv = new GridView();

gv.DataSource = dt;

gv.DataBind();

Unfortunately, this will fail.  We’re only passing three variables, not the four (the refcursor) needed.  Enterprise Library (as of 3.1 May 2007) doesn’t have a DbType for Cursor (similar to the OracleType.Cursor part of System.Data.OracleClient). 

To get it to compile, our Stored Procedure needs one more parameter.   What it needs, after trial and error, it simply a pacifier.  Placing null as the recipient of the refcursor parameter keeps it happy and compiles.

 

DbCommand cmd =

db.GetStoredProcCommand(“GetMathData”,

4,

“2006-2007″,

[somenumber],

null);

 

So, you may be wondering where the data comes from… since we’re never handling the refcursor.  From what I’ve found (which isn’t much), if you are using ExecuteDataSet, the first output cursor (resultset_out out TYPES.cursorType) that is named with “_out” will become the returned DataSet.  The fact it worked had nothing to do with technology and all to do with my variable name.  *sigh*  Also, as pointed out in Tim Hollandar’s blog, this limits you to retrieving only the “_out” cursor… and only one.

 

After all that fun, I think I’ll walk into the back room and hug the SQL Server.

 

Ref #1: For an excellent article on hacking Enterprise Library to work with ODP.NET, check out Alex’s post out on CodeProject.  Looks good and I’m sure it works—just not something I want to get into the habit of right now EVERY TIME ODP or EntLibrary updates.  Seriously though, this stuff should work out of the box. *coughEntLibTeam!cough*

 

Ref #2 : This is a custom package definition that holds the ref cursor type.  I added it using:

CREATE OR REPLACE PACKAGE Types AS
     TYPE cursorType IS REF CURSOR;
END Types;

About these ads
  1. rc
    January 5, 2008 at 2:46 pm

    If you use Oracle 9, 10 or 11 you don’t have to do:

    , resultset_out out TYPES.cursorType)

    You can us:

    , resultset_out out sys_refcursor)

    When you do this, you no longer need package Types so you can do

    Drop package Types;

    Why don’t want to you use the OracleType.Cursor of System.Data.OracleClient? We sometimes return 15 sys_refcursors from our PL/SQL-procedures, no problems!

    The lay out of your PL/SQL is strange.

    Do:

    create or replace procedure …
    as
    begin
    ….
    end;

    The begin and the end should be aligned. They belong together, so show this in your code!

  2. January 5, 2008 at 3:27 pm

    @rc-

    re: sys_refcursor : Cool, I’ll have to try that. Most of this is against Oracle 10g databases.

    Re: OracleType.Cursor : As I mentioned in the post, Enterprise Library doesn’t support OracleType.Cursor at this point; if I was simply calling OracleClient, then that’d be the way to go.

    Re: formatting and such : It depends on training and coding standards–the above follows our office coding standards. *shrug* When it’s converted to 0’s and 1’s, it’ll all look the same. :) Our BEGIN statements end follow the name of the procedure.

  3. rc
    January 5, 2008 at 6:02 pm

    Strange office standards.

    Also you seems amazed that you have to use an out parameter to return something. But isn’t it normal that you have to define parameters if you want to return something?

    public void DoSomething()
    {

    }

    Doesn’t return anything, does it? (except maybe a exception). You have to define a return type or out parameters!

    I can’t think of any language (C#, Delphi, Java? ) where a procedure returns something without a parameter or a return type? I don’t know any T-SQL but defining parameters for methods, procedures and functions is normal.

    How can you know in T SQL from the outside what a procedure will return? The list of parameters is a kind of interface for the outside world isn’t it? It works this way in Java, C#, Delphi and PL/SQL. In Java, C#, Delphi and PL/SQL you have to know the parameters if you want to use the method, procedure or function.

  4. January 5, 2008 at 6:31 pm

    @rc-

    Agreed with strange office standards, but they are what they are. (^_^) On a day to day basis, it’s not something I really even consider. I’m sure you’ve worked places before that things may have not made sense, but they were what they were. :)

    As far as finding it “amazing”, I’m not sure of that–as the post states, my Oracle experience is somewhat lacking–I stick to MSFT SQL. I lucked onto the _out actually outputting the results–that’s what amazed me. Until this point, most of my EntLib experience was with SQL Server 2000/2005 and MySQL. The focus of the post revolved around the pain of using EntLib to populate a dataset directly–not so much code formatting or how it was being output or the core Oracle libraries. :)

    As far as having a void stored procedure, TSQL is somewhat like that; you can have a stored procedure and when you can it, it simply expects that, unless you have specified that it’s scalar, that you’ll get tabular data back. :) You CAN specify it, but it’s not required.

    In the past four months since the article, I’ve had a bit more experience with Oracle, but Enterprise Library has yet to update itself–Oracle and Microsoft’s… antics… continue to limit that progress.

    I’ve replaced a few of these procedures using the ODP.NET libraries and others have been encapsulated so we can LINQ into them.

    When I get to the office on Monday, I’ll post up a comment with what TSQL code would look like for this example–and how it works without having an output parameter specified.

  1. No trackbacks yet.
Comments are closed.
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: