Enterprise Library and Oracle Stored Procedure Record Sets
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
pupil_id as StudentId,
teacher_id as TeacherId,
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;
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);
Now that we have that taken care of, let’s use it in Enterprise Library. Be sure to import .Data and .Common.
Database db =
DbCommand cmd =
DataTable dt =
GridView gv = new GridView();
gv.DataSource = dt;
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 =
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;