Dashboard > iBATIS DataMapper > Home > Frequently Asked Questions > How do I use an Oracle Ref Cursor? cs
How do I use an Oracle Ref Cursor? cs
Added by Roberto Rabe, last edited by Roberto Rabe on Jun 06, 2005  (view change)
Labels: 
(None)


.NET DataMapper SVN Revision 169743 (DataMapper 1.2)

Note: This FAQ is based on .NET DataMapper code as of SVN Revision 169743 (DataMapper 1.2), a development revision of the .NET DataMapper.

With the changes made to the .NET DataMapper's handling of parameterMaps, utilizing an Oracle ref cursor to return a list of objects is now very simple to do. For our example, we will use the code found in the .NET DataMapper's NUnit test project for Oracle using the System.Data.OracleClient 1.0 and ODP.NET 10g providers.

First, let's define a really basic Oracle package and stored procedure to return a simple ref cursor that lists all records in our Accounts table:

REF_CURSOR_TEST
CREATE OR REPLACE PACKAGE REF_CURSOR_TEST IS
    
    TYPE T_ACCOUNTS_CURSOR IS REF CURSOR;
    PROCEDURE GET_ACCOUNTS_PROCEDURE (P_ACCOUNTS OUT T_ACCOUNTS_CURSOR);
    
    END REF_CURSOR_TEST;
    /
    
    CREATE OR REPLACE PACKAGE BODY REF_CURSOR_TEST IS
    
    PROCEDURE GET_ACCOUNTS_PROCEDURE (
    P_ACCOUNTS OUT T_ACCOUNTS_CURSOR
    ) AS
    BEGIN
    OPEN P_ACCOUNTS FOR
    SELECT
    Account_ID as Id,
    Account_FirstName as FirstName,
    Account_LastName as LastName,
    Account_Email as EmailAddress
    FROM Accounts
    ORDER BY Account_ID;
    END GET_ACCOUNTS_PROCEDURE;
    
    END REF_CURSOR_TEST;
    /

Next, we'll create our <procedure> statement and its related <parameterMap>. All we really need to do is specify the package.procedure name in the <procedure> statement and identify the direction, column name, and RefCursor dbType of our parameter:

System.Data.OracleClient 1.0 example
...(snip)...
    
    <procedure id="GetAllAccountsViaStoredProcRefCursor"
    parameterMap="stored-proc-refcursor-param"
    resultClass="Account">
    REF_CURSOR_TEST.GET_ACCOUNTS_PROCEDURE
    </procedure>
    
    ...(snip)...
    
    <parameterMap id="stored-proc-refcursor-param">
    <parameter property="P_ACCOUNTS" direction="Output" column="P_ACCOUNTS" dbType="Cursor"/>
    </parameterMap>
    
    ...(snip)...

That's pretty much it! We can now obtain our list of Accounts by passing in a null property value to the SqlMapper's QueryForList method. This null property value acts as "placeholder" parameter that the DataMapper uses for the ref cursor (it just turns it into a DBNull.Value when setting the command parameter value):

C# example
Hashtable param = new Hashtable();
    param.Add("P_ACCOUNTS",null);
    
    IList accounts = sqlMap.QueryForList("GetAllAccountsViaStoredProcRefCursor", param);
    int resultCount = accounts.Count;
    Account firstAcct = accounts[0] as Account;

Now what about also passing in a parameter to the procedure? It's just as you'd expect! We simply add another parameter to the parameterMap (note: the ref cursor output parameter needs to be defined as the first parameter):

REF_CURSOR_TEST modified
CREATE OR REPLACE PACKAGE REF_CURSOR_TEST IS
    
    TYPE T_ACCOUNTS_CURSOR IS REF CURSOR;
    PROCEDURE GET_ACCOUNTS_PROCEDURE (P_ACCOUNTS OUT T_ACCOUNTS_CURSOR);
    PROCEDURE GET_ACCOUNT_PROCEDURE (P_ACCOUNTS OUT T_ACCOUNTS_CURSOR, P_ACCOUNT_ID IN INTEGER);
    
    END REF_CURSOR_TEST;
    /
    
    CREATE OR REPLACE PACKAGE BODY REF_CURSOR_TEST IS
    
    PROCEDURE GET_ACCOUNTS_PROCEDURE (
    P_ACCOUNTS OUT T_ACCOUNTS_CURSOR
    ) AS
    BEGIN
    OPEN P_ACCOUNTS FOR
    SELECT
    Account_ID as Id,
    Account_FirstName as FirstName,
    Account_LastName as LastName,
    Account_Email as EmailAddress
    FROM Accounts
    ORDER BY Account_ID;
    END GET_ACCOUNTS_PROCEDURE;
    
    PROCEDURE GET_ACCOUNT_PROCEDURE (
    P_ACCOUNTS OUT T_ACCOUNTS_CURSOR,
    P_ACCOUNT_ID IN INTEGER
    ) AS
    BEGIN
    OPEN P_ACCOUNTS FOR
    SELECT
    Account_ID as Id,
    Account_FirstName as FirstName,
    Account_LastName as LastName,
    Account_Email as EmailAddress
    FROM Accounts
    WHERE Account_ID = P_ACCOUNT_ID
    ORDER BY Account_ID;
    END GET_ACCOUNT_PROCEDURE;
    
    END REF_CURSOR_TEST;
    /
ODP.NET 10g example
...(snip)...
    
    <procedure id="GetAccountViaStoredProcRefCursor"
    parameterMap="stored-proc-refcursor-param-with-input"
    resultClass="Account">
    REF_CURSOR_TEST.GET_ACCOUNT_PROCEDURE
    </procedure>
    
    ...(snip)...
    
    <parameterMap id="stored-proc-refcursor-param-with-input">
    <parameter property="P_ACCOUNTS" direction="Output" column="P_ACCOUNTS" dbType="RefCursor"/>
    <parameter property="P_ACCOUNT_ID" direction="Input" column="P_ACCOUNT_ID" dbType="Int32"/>
    </parameterMap>
    ...(snip)...
C# example with Input parameter
Hashtable param = new Hashtable();
    param.Add("P_ACCOUNTS",null);
    param.Add("P_ACCOUNT_ID",1);
    
    IList accounts = sqlMap.QueryForList("GetAccountViaStoredProcRefCursor", param);
    int resultCount = accounts.Count;

Returning 1 record is perhaps not a good use for a ref cursor, but it'll do for this simple example.


Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators