How to write an Oracle stored procedure to work with Telerik Reporting Wizard?

[Nicholas]

I am trying to create a report that calls a stored procedure for its data source... this works fine with SQL Server, however when I tried this with Oracle i run into some problems.

From my understanding, Oracle stored procedures need to return a ref cursor in order to return the results of a SELECT statement. But when I use the Telerik report wizard to call the stored procedure, it doesn't show me the rows in my select as the output parameters. It only shows the ref cursor.

Can anyone provide a simple Oracle stored procedure and instructions on how to connect to it using the Telerik report wizard?

Greatly appreciate any help, thanks!

[Peter]

Hi Nicholas,

You are on the right track here - to return a result set from an Oracle stored procedure you need to declare an output parameter of type sys_refcursor. Here is a sample stored procedure that retrieves some data from the sample scott/tiger schema:

create or replace

procedure get_dept_emps(p_dept_name in nvarchar2,

                 p_emp_name in nvarchar2,

                 p_cursor out sys_refcursor) is

begin

  open p_cursor for

  select dept.deptno as dept_no,

       dept.dname as dept_name,

       emp.empno as emp_no,

       emp.ename as emp_name

  from dept

  join emp

  on dept.deptno = emp.deptno

  where dept.dname like p_dept_name

  and emp.ename like p_emp_name;

end;

The steps how to setup the stored procedure in SqlDataSource Wizard are described in the following help topic from the online documentation: SqlDataSource Wizard. Just remember on the Configure Data Source Command page to choose the Stored Procedure option and pick your stored procedure from the drop-down list. A common mistake made by some users is to specify the name of the stored procedure in the text area below the Select Statement option, which would not produce the desired result, because SqlDataSource would treat that as a regular select statement and fail to execute it. Please, check the attached sample report which demonstrates this. You might need to modify the connection string in the SqlSataSource component to reflect the location where your Oracle database is installed.

Additionally make sure that the used OLE DB provider supports the Oracle-specific type sys_refcursor.

Best wishes,
Peter
the Telerik team