Telerik OpenAccess ORM - Executing an Oracle stored procedure that returns result sets

by OpenAccess ORM Team | Comments 4

In this blog post I will describe how easy it is, to work with the Telerik OpenAccess lower level ADO API and Oracle Stored Procedures that are returning SYS_REFCURSOR as out parameters.

 

The Setup

For the following examples we are using a basic Console Application project, which should be enough for the purpose of this blog. We have added a Domain Model to our Solution with two Domain Classes(‘Category’ and ‘Product’). Further we added the ODP.NET Driver from Oracle( you can get it from here), which should be installed on your machine as well.

 

Project

 

On the Oracle database, we have created 2 stored procedures.

The first one we create is a simple stored procedure having an SYS_REFCURSOR out parameter. We return all category rows via this cursor.

   1: CREATE OR REPLACE PROCEDURE SPSingleRefCur(catCur OUT SYS_REFCURSOR )

Senior Software Developer
Telerik OpenAccess ORM

4 Comments

Roger
Can you include the solution for the same?
Kosta Hristov
Hello Roger,
We created a project on the topic, it can be found in our Code Library :
http://www.telerik.com/community/code-library/orm/general/telerik-openaccess-orm---executing-an-oracle-stored-procedure-that-returns-result-sets.aspx
 
Note that there may be differences between the code provided in this blog post, and the actual code in the project. You can check the project description for more information.
If you have any questions, do not hesitate to ask.
 
Best Regards,
OpenAccess ORM team
Roger
I am trying to implement it in a Silverlight RIA WCF project generated using OpenAccess. Any suggestions on it. 
Thanks 
Ralph Waldenmaier
Hello Roger,
I've created an example method that shows how this could be done. I used the example that was published by Kosta.
public IQueryable<Employee> GetValuesFromSP() 
        {             
                OracleParameter cursorParameter = new OracleParameter(); 
                cursorParameter.ParameterName = "empCur"; 
                cursorParameter.Direction = ParameterDirection.Output; 
                cursorParameter.OracleDbType = OracleDbType.RefCursor; 
 
                return this.DataContext.ExecuteQuery<Employee>("SPSingleRefCur", CommandType.StoredProcedure, cursorParameter).AsQueryable < Employee>(); 
        }   

If you have any questions, do not hesitate to ask.
 
Best Regards,
OpenAccess ORM team

Comments

  1.    
     
    		 
    		  
       
  3. (optional, emails won't be shown on public pages)
  4. (optional)
Read more articles by OpenAccess ORM Team - or - read latest articles in Developer Tools
Bloggers Archive Tags