Skip to Content
avatar image
Former Member

Crystal Report using Ref Cursor OLE DB failed to retrieve

Dear Experts,

Im using Visual Studio 2010 CR version 13, while trying connect Ref cusror Proc getting below attached error. Please suggest to resolve this issues.

before one year its working. now im trying to modify the script, but getting error. Please advise.

1.below Image, saved data at the time of designing its working.

2.Now im trying to modify the report once i refreshed, gettting below error.

3.Trying with new connection, can u help me to select correct provider for Ref Cursor.

below provider selection, which one i need to be select for Ref Cursor Procedure working Perfectly.

4.Is this correct provider for Ref Cursor ?

before it's only i was connected

5. Once i select the Ref Cursor Proc, check that Report preview while getting below error.

Please help me to resolve this issues.

Thanks in advance,

Mani.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Sep 06, 2017 at 04:24 PM

    Hi Mani,

    CR Designer needs a REF Cursor to hold the result set in so you may need to update your SP.

    See this PDF in text format...

    Crystal Reports
    Oracle Stored Procedures
    Overview
    This document contains information about which database drivers support
    Oracle stored procedures in Crystal Reports 9 and 10. Also, this document
    contains information on how to report off an Oracle stored procedure, including
    a list of minimum requirements.
    NOTE A native or ODBC connection is recommended for reporting off Oracle stored procedures
    for full functionality to be available.
    Contents
    INTRODUCTION............................................................................................2
    DATABASE DRIVERS THAT SUPPORT ORACLE STORED PROCEDURES...........2
    Crystal Reports 9 .........................................................................................2
    Crystal Reports 10 .......................................................................................3
    REQUIREMENTS OF ORACLE STORED PROCEDURES.....................................3
    Strongly Bound REF CURSORs versus Weakly Bound REF CURSORs .....4
    CREATING AN ORACLE STORED PROCEDURE...............................................5
    Sample A: Use with ODBC and native connections in Crystal Reports ......5
    Sample B: Weakly Bound REF CURSOR ....................................................6
    Sample C: Stored procedure created within a package...............................7
    EXECUTING AN ORACLE STORED PROCEDURE IN SQL*PLUS .......................8
    CREATING A CRYSTAL REPORT OFF AN ORACLE STORED PROCEDURE.........9
    FINDING MORE INFORMATION ......................................................................9
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 2
    cr_oracle_stored_procedures.pdf
    Introduction
    This article provides information for Oracle stored procedures and Crystal
    Reports (CR). You will find detailed information on the following:
    • Crystal Reports 9 and 10 database drivers that support Oracle stored
    procedures
    • Requirements when reporting from Oracle stored procedures
    • Creating Oracle stored procedures
    • Executing Oracle stored procedures in SQL*Plus to troubleshoot
    Database Drivers that Support Oracle Stored Procedures
    Crystal Reports 9
    For Crystal Reports 9, refer to Table A: Database drivers and Oracle, to
    determine which database drivers can report off stored procedures in the
    specified versions of Oracle.
    Table A: Database drivers and Oracle
    Database Drivers
    (32-bit only)
    Oracle
    7.x
    Oracle
    8.0.5
    Oracle
    8.0.6
    Oracle
    8.1.x
    Oracle
    9.x
    Native Oracle
    Driver
    (Crdb_oracle.dll)
    No No Yes Yes Yes
    CR Oracle 4.10
    ODBC Driver
    No No Yes Yes Yes
    Oracle ODBC
    Driver
    No No No Yes Yes
    NOTE Only the most recent version of the Oracle 8i ODBC driver are able to work with stored
    procedures.
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 3
    cr_oracle_stored_procedures.pdf
    Crystal Reports 10
    For Crystal Reports 10, refer to Table B: Database drivers and Oracle, to
    determine which database drivers can report off stored procedures in the
    specified versions of Oracle.
    Table B: Database drivers and Oracle
    Database Drivers
    (32-bit only)
    Oracle
    7.x
    Oracle
    8.0.5
    Oracle
    8.0.6
    Oracle
    8.1.7
    Oracle
    9.x
    Native Oracle
    Driver
    (Crdb_oracle.dll)
    No No No Yes Yes
    CR Oracle 4.20
    ODBC Driver
    No No No Yes Yes
    Oracle ODBC
    Driver
    No No No Yes Yes
    NOTE Only the most recent version of the Oracle 8i ODBC driver will be able to work with stored
    procedures.
    Requirements of Oracle Stored Procedures
    In order for Crystal Reports to report off Oracle stored procedures, all of the
    following requirements must exist.
    • To use an ODBC connection to access an Oracle stored procedure, you
    must create a package that defines the REF CURSOR. This REF CURSOR
    must be strongly bound to a static pre-defined structure (see Strongly Bound
    REF CURSORs vs Weakly Bound REF CURSORs). This package must be
    created separately and before the creation of the stored procedure.
    • A native connection to Oracle in Crystal Reports 9 and Crystal Reports 10
    can be used to access an Oracle stored procedure that was created within a
    package and also an Oracle stored procedure that references a weakly bound
    REF CURSOR
    • The stored procedure must have a parameter that is a REF CURSOR type.
    Crystal Reports uses this parameter to access and define the result set that
    the stored procedure returns.
    • The REF CURSOR parameter must be defined as IN OUT (read/write
    mode). After the stored procedure has opened and assigned a query to the
    REF CURSOR, Crystal Reports will perform a FETCH call for every row
    from the query’s result. This is why the parameter must be defined as IN
    OUT.
    • The parameters can only be defined as IN (input) parameters. Crystal
    Reports is not designed to work with OUT parameters.
    • The REF CURSOR variable must be opened and assigned its query within
    the procedure.
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 4
    cr_oracle_stored_procedures.pdf
    • The stored procedure can only return one record set. The structure of this
    record set must not changed, based on parameters.
    • The stored procedure cannot call another stored procedure.
    • If using an ODBC driver, it must be the CR Oracle ODBC driver installed
    by Crystal Reports.
    • If you are using the CR ODBC driver, verify that the option ‘Procedure
    Return Results’ is selected as ‘On’ in the ODBC Driver Configuration setup
    under the ‘Advanced’ tab.
    • If you are using the native Oracle driver and using hard-coded date selection
    within the stored procedure, the date selection must use either a string
    representation format of YYYY-DD-MM (where the date field = 2004-01-
    01) or the To_Date formula function with the same format specified (where
    date field = To_Date(2004-01-01’,’YYYY-MM-DD’).
    • Most importantly, the stored procedure must be able to execute successfully
    in Oracle’s SQL *Plus utility.
    If all of these requirements have been met, verify that the database driver that
    you are using works with that version of Oracle.
    Strongly Bound REF CURSORs versus Weakly
    Bound REF CURSORs
    When declaring the REF CURSOR in an Oracle package, the REF CURSOR
    may be strongly bound or it may be weakly bound.
    A strongly bound REF CURSOR is bound with the same structure that is used
    by the outputted SELECT statement in the Oracle stored procedure.
    CREATE OR REPLACE PACKAGE Test_Package
    AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
    END Test_Package;
    /
    The example code in green displays the REF CURSOR strongly bound. In this
    example, the REF CURSOR is bound with the row structure of the Test_Table
    table.
    A weakly bound REF CURSOR is not assigned a structure in the Oracle
    package.
    CREATE OR REPLACE PACKAGE Test_Package
    AS TYPE Test_Type IS REF CURSOR;
    END Test_Package;
    /
    Although the REF CURSOR is not assigned a structure in the Oracle package,
    the Oracle stored procedure itself must return a record set with a structure that
    does not change based on parameters.
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 5
    cr_oracle_stored_procedures.pdf
    Creating an Oracle Stored Procedure
    Samples of stored procedures and their steps on how to create them are
    provided. These are samples of Oracle stored procedures that will return a result
    set in Crystal Reports. If these samples were executed in the Oracle connection
    utility, SQL *Plus, they should run successfully.
    NOTE These sample stored procedures are provided as a tip. Refer to your Oracle
    documentation for more information on creating stored procedures.
    Sample A: Use with ODBC and native
    connections in Crystal Reports
    1. Create a table.
    CREATE TABLE Test_Table
    (ID number(5),
    Firstname varchar2(30),
    Lastname varchar2(30),
    Birthday date);
    2. Insert values into the table.
    INSERT INTO Test_Table VALUES
    (1, 'Christopher', 'Jones', '01-Nov-70');
    INSERT INTO Test_Table VALUES
    (2, 'Maria', 'Marshall', '02-Jan-77');
    INSERT INTO Test_Table VALUES
    (3, 'Jonathan', 'Campbell', '09-Aug-75');
    INSERT INTO Test_Table VALUES
    (4, 'Julie', 'Gagnon', '23-Dec-72');
    INSERT INTO Test_Table VALUES
    (5, 'Daemon', 'Thompson', '11-Feb-69');
    3. Create the Package.
    CREATE OR REPLACE PACKAGE Test_Package
    AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
    END Test_Package;
    /
    4. Create the stored procedure.
    CREATE OR REPLACE PROCEDURE Test_Procedure (
    Test_Cursor IN OUT Test_Package.Test_Type,
    Test_Parameter IN Test_Table.ID%TYPE)
    AS
    BEGIN
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 6
    cr_oracle_stored_procedures.pdf
    OPEN Test_Cursor FOR
    SELECT *
    FROM Test_Table
    WHERE Test_Table.ID = Test_Parameter;
    END Test_Procedure;
    /
    5. Once the stored procedure is successfully created on your Oracle database,
    execute the stored procedure.
    Sample B: Weakly Bound REF CURSOR
    Only use this sample with a native connection to Crystal Reports 9 or later.
    1. Create a table.
    CREATE TABLE Test_Table
    (ID number(5),
    Firstname varchar2(30),
    Lastname varchar2(30),
    Birthday date);
    2. Insert values into the table.
    INSERT INTO Test_Table VALUES
    (1, 'Christopher', 'Jones', '01-Nov-70');
    INSERT INTO Test_Table VALUES
    (2, 'Maria', 'Marshall', '02-Jan-77');
    INSERT INTO Test_Table VALUES
    (3, 'Jonathan', 'Campbell', '09-Aug-75');
    INSERT INTO Test_Table VALUES
    (4, 'Julie', 'Gagnon', '23-Dec-72');
    INSERT INTO Test_Table VALUES
    (5, 'Daemon', 'Thompson', '11-Feb-69');
    3. Create the Package.
    CREATE OR REPLACE PACKAGE Test_Package
    AS TYPE Test_Type IS REF CURSOR;
    END Test_Package;
    /
    4. Create the stored procedure.
    CREATE OR REPLACE PROCEDURE Test_Procedure (
    Test_Cursor IN OUT Test_Package.Test_Type,
    Test_Parameter IN Test_Table.ID%TYPE)
    AS
    BEGIN
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 7
    cr_oracle_stored_procedures.pdf
    OPEN Test_Cursor FOR
    SELECT *
    FROM Test_Table
    WHERE Test_Table.ID = Test_Parameter;
    END Test_Procedure;
    /
    5. Once the stored procedure is successfully created on your Oracle database,
    execute the stored procedure.
    Sample C: Stored procedure created within a
    package
    Only use this sample with a native connection to Crystal Reports 9 or later.
    1. Create a table.
    CREATE TABLE Test_Table
    (ID number(5),
    Firstname varchar2(30),
    Lastname varchar2(30),
    Birthday date);
    2. Insert values into the table.
    INSERT INTO Test_Table VALUES
    (1, 'Christopher', 'Jones', '01-Nov-70');
    INSERT INTO Test_Table VALUES
    (2, 'Maria', 'Marshall', '02-Jan-77');
    INSERT INTO Test_Table VALUES
    (3, 'Jonathan', 'Campbell', '09-Aug-75');
    INSERT INTO Test_Table VALUES
    (4, 'Julie', 'Gagnon', '23-Dec-72');
    INSERT INTO Test_Table VALUES
    (5, 'Daemon', 'Thompson', '11-Feb-69');
    3. Create the Package.
    CREATE OR REPLACE PACKAGE Test_Package
    AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
    PROCEDURE Test_Procedure (
    Test_Cursor IN OUT Test_Type,
    Test_Parameter IN Test_Table.ID%TYPE
    );
    END Test_Package;
    /
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 8
    cr_oracle_stored_procedures.pdf
    4. Create the Package Body (With The Code For The Stored Procedure).
    CREATE OR REPLACE PACKAGE BODY Test_Package
    AS
    PROCEDURE Test_Procedure (
    Test_Cursor IN OUT Test_Type,
    Test_Parameter IN Test_Table.ID%TYPE
    ) IS
    BEGIN
    OPEN Test_Cursor FOR
    SELECT *
    FROM Test_Table
    WHERE Test_Table.ID = Test_Parameter;
    END Test_Procedure;
    END Test_Package;
    /
    5. Once the stored procedure is successfully created on your Oracle database,
    execute the stored procedure.
    Executing an Oracle Stored Procedure in SQL*Plus
    The following script executes the sample stored procedures created earlier in
    this document. This script runs in Oracle's native connection utility, SQL*Plus.
    Remember to change the <parameter value> to a valid parameter value (a
    number between 1 and 5).
    SET SERVEROUTPUT ON
    declare
    test_cursor test_package.test_type;
    resultset test_cursor%rowtype;
    begin
    test_procedure(test_cursor, <parameter value>);
    if not test_cursor%isopen then
    dbms_output.put_line('the cursor is not open');
    else
    dbms_output.put_line('the cursor is open');
    end if;
    fetch test_cursor into resultset;
    while test_cursor%found loop
    dbms_output.put_line(resultset.ID);
    dbms_output.put_line(resultset.Firstname);
    dbms_output.put_line(resultset.Lastname);
    dbms_output.put_line(resultset.Birthday);
    Crystal Reports Oracle Stored Procedures
    2/18/2004 2:55 PM Copyright © 2004 Business Objects. All rights reserved. Page 9
    cr_oracle_stored_procedures.pdf
    www.businessobjects.com
    The Business Objects product and technology are protected by US patent numbers 5,555,403; 6,247,008;
    6,578,027; 6,490,593; and 6,289,352. The Business Objects logo, the Business Objects tagline, BusinessObjects,
    BusinessObjects Broadcast Agent, BusinessQuery, Crystal Analysis, Crystal Analysis Holos, Crystal Applications,
    Crystal Enterprise, Crystal Info, Crystal Reports, Rapid Mart, and WebIntelligence are trademarks or registered
    trademarks of Business Objects SA in the United States and/or other countries. Various product and service
    names referenced herein may be trademarks of Business Objects SA. All other company, product, or brand
    names mentioned herein, may be the trademarks of their respective owners. Specifications subject to change
    without notice. Not responsible for errors or omissions. Copyright © 2004 Business Objects SA. All rights reserved.
    fetch test_cursor into resultset;
    end loop;
    end;
    /
    NOTE If the script is executing correctly but you do not see any results displayed on the screen,
    enter SET SERVEROUTPUT ON at the SQL> prompt to display results to the screen.
    Creating a Crystal Report off an Oracle Stored Procedure
    These steps are based on Crystal Reports 9 and 10. However, they may be
    applicable to other versions.
    1. In Crystal Reports, click the File menu, and then click New.
    2. Click As a Blank Report, and then click OK.
    3. In the Database Expert dialog box, find your data source. Double-click on
    the data source and you will see a list of the available Schemas, choose the
    Schema that you want and then you will see the Stored Procedures node.
    Double-click on that and you will see the list of stored procedures.
    4. Move the procedure(s) you want to report off into the Selected Tables box.
    If the stored procedure has a parameter you will be prompted to enter a
    value at this point.
    5. Click OK to close the Enter Parameter Values dialog box, and then click
    OK to close the Database Expert.
    You can now design a Crystal report off an Oracle Stored Procedure.
    NOTE If you do not see the Stored Procedures node in the Database Expert do the following. On
    the File menu, click Options. On the Database tab, under the Show section, select the
    Stored Procedures check box.
    Finding More Information
    For more information regarding the creation of Oracle Stored procedures, refer
    to www.oracle.com.

    Or try searching for it using Google.

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 30, 2017 at 09:52 PM

    Microsoft's Oracle Driver only supported Oracle 7, you need to update to a current Oracle OLE DB Provider.

    TO be supported be sure to get the latest CR for Runtime:

    https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

    Don

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Don,

      thanks for your reply, Currently I'm using VS 2010 CR latest version (CRforVS_13_0_20) and 11g Client ODTwithODAC112021, Oracle Provider for OLE DB (11.2.0) getting the below error,

      did i select any wrong provider? please let me know to clear this one...

      How to update current Oracle OLE DB Provider ?

      (Currently im using my local system Oracle 11g client ODTwithODAC112021)

      Please suggest me to update which one below,

      11g Client,

      12c Client,

      Thanks in advance,

      Mani

      msdaora.png (38.7 kB)
      msprovider.png (123.2 kB)
  • Aug 31, 2017 at 08:30 PM

    Yes, you are selecting MS' Oracle provider,look at the last one listed in the screen shot, that's the one you need to use.

    Don

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Don,

      As per your comment, i have selected the 'Oracle Provider for OLE DB' and select the listed procedure to design the report, but getting below error.i can't to Move selected tables space.

      Suppose if i select the MS OLE DB provider for Oracle, i can select and use (before that i used to design & display the report working as expected but now its not working).

      Thanks in advance

      Regards,

      Mani

      provider.png (96.3 kB)
      pro-err.png (106.1 kB)