Going back today to see whats what in calling Oracle stored procedures w/wo ref cursors using the DAAB, and if my understanding is correct, then in general all [Oracle] stored procs, must have an output ref cursor defined called cur_OUT.

The exception as far as I can see is as follows;

You have a stored proc which returns 1 or more ref cursors, and, you add those in/out ref cursor parameters to the command wrapper in your code (which sets the isRefCursorAdded field of the CommandWrapper object, which in turn is referenced by the XXXDatabase object’s PrepareCWRefCursor method)

While this kind of makes sense, given that Oracle ref cursor parameters have to be defined as OracleType.RefCursor as opposed to SqlType.Cursor, and also given that one goal of the DABB is data provider abstraction – the solution seems a bit dumb.

Thinking off the top of my head, maybe a better solution would have been be to ask the command wrapper to add a refcursor type parameter [using say, a specific method call], which in the case of the SqlDatabase version would do nothing, but would be implemented correctly in the Oracle and ODP versions. Of course, one of the nice things about the ENTLIB is that you get the source, so can be modified to suit, which is how I added XXXDatabase support for the Oracle ODP data provider.

A trade off of this kind of solution is that if your code is generic (you aren’t branching based on the type of XXXDatabase object), you will have extra calls to this “AddRefCursorParameter” method for each ref cursor, which in fact mean nothing (and would do nothing) in the case of a SQL implementation – which may be confusing.

I think I’ll try this out, and post it up here if it works out nicely.

Update 1st Dec 2006:-

Looks like I wasn’t the first to think of this. Rob Edwards posted here proposing just the same thing.
[http://oneprogrammer.blogspot.com/2005/04/writeup-for-oracle-changes.html].