DAAB with Oracle ref cursors

  • Thread starter Thread starter Dmitry
  • Start date Start date
D

Dmitry

Hi,

Can anybody send me an example of calling an Oracle stored procedure
which returns a ref cursor using Enterprise Library DAAB? More
precisely, I need to know how to call both procedures:

PROCEDURE GET_USER_BYID( P_USER_ID_IN IN NUMBER, cur_OUT OUT REF CURSOR)

and functons:

FUNCTION GET_USER_BYID( P_USER_ID_IN NUMBER ) RETURN REF CURSOR


Any help would be greatly appreciated.

Thanks,
Dmitry.
 
The older DAAB (non Enterprise library) requires a bit of tooling to get it
to work 100% with Oracle. Here are some rules I know of:

1. If you are using OLEDB, do not include the ref cursor in your parameters.
2. If you are using OracleClient, do include the ref cursor.

I am not sure why this is.

A ref cursor, in Oracle, is a result set. OLEDB translates this to a
DataTable. OracleClient also does this, but you have to pass the parameter to
get this functionality. NOTE: You do not have to turn the ref cursor into a
DataTable explicitly, as this is done underneath the hood. The only
difference is whether or not you actually have to create a parameter and pass
it.

If you are on the Enterprise Library version, I have no clue, as I have not
worked with Oracle on it.

That should help you get started.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Thanks for the quick reply. I'm using EL 2.0 with MS Provider for
Oracle. I did quite a few experiment trying to make my functions and
procedures work and found out that one way to make functions work is to
call DiscoverParameters and then set the appropriate parameters values:

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = b.GetStoredProcCommand( "MyPLSQLfunction");
db.DiscoverParameters( dbCommand );

I guess it requires an additional round-trip to a server. I'm curious
about performance implication of it; does DAAB always implicitly call
DiscoverParameters it before calling a stored procedure?

Thanks,
Dmitry.
 
Back
Top