Returning ref cursor from oracle stored procedure by using DAAB from MS EntLib 4.1

Question

Is it possible to get ref cursor from oracle stored procedure by using DAAB from Microsoft Enterprise Library 4.1?

Answer 1

Yes, surely you can.

PROCEDURE load_all (
    p_row_start          IN     number,
    p_page_size          IN     number,

    cur_out              OUT    sys_refcursor
) AS
...

And on the C# side:

DbCommand cmd = db.GetStoredProcCommand("my_package.load_all", paramValues))
IDataReader r = db.ExecuteReader(cmd);
...

RefCursor param supposed to be named "cur_out".

Answer 2

DAAB does not provide a capability for us to get the Ref Cursor. The issue is System.Data.Common does not have DbType.Cursor or DbType.RefCursor type. So, there is a bit of tweaking required in the DAAB 4.1 source code. Please follow the steps provided in the following link. It works like a piece of cake.

http://www.codeproject.com/KB/database/DAAB_On_ODPNet.aspx