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

2 posts, 0 answers
  1. Nicholas
    Nicholas avatar
    11 posts
    Member since:
    Jan 2011

    Posted 19 Oct 2011 Link to this post

    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!
  2. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 24 Oct 2011 Link to this post

    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

    Q2’11 SP1 of Telerik Reporting is available for download (see what's new). Get it today.

  3. DevCraft banner
Back to Top