SAP Knowledge Base Article - Public

1181947 - Oracle Stored Procedure Requirements for Crystal Reports

Symptom

  • Is it possible to report off an Oracle Stored Procedure in Crystal Reports?
  • What are the requirements for an Oracle Stored Procedures to be able to report of it in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
        
  • Oracle 11g
  • Oracle 12c

Resolution

  • In order for Crystal Reports to report off an Oracle stored procedure, all of the following requirements must be satisfied:
         
    1. When using 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. This package must be created separately and before the creation of the stored procedure.
          
    2. When using a native connection, it is possible to report off the Oracle stored procedure that was created within a package and also an Oracle stored procedure that references a weakly bound REF CURSOR.
       
    3. 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.
       
    4. 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.
       
    5. The parameters can only be defined as IN (input) parameters. Crystal Reports is not designed to work with OUT parameters.
       
    6. The REF CURSOR variable must be opened and assigned its query within the procedure.
       
    7. The stored procedure can only return one record set. The structure of this record set must not change based on parameters.
       
    8. The stored procedure cannot call another stored procedure.
       
    9. 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.
       
    10. 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’).
        
    11. Most importantly, the stored procedure must be able to execute successfully in Oracle’s SQL *Plus utility.
         
    12. If all of the above requirements have been met, verify that the database driver that you are using works with that version of Oracle outside of Crystal Reports.
          
         
  • For more information on using Oracle Stored Procedure with Crystal Reports, see the document: Oracle Stored Procedures and Crystal Reports
         
    Note: The document was created for older versions of Crystal Reports, but the information it contains is still valid for Crystal Reports 2008, 2011, 2013, and 2016
 

Keywords

CR, SP, stored proc, ora, conn, connection , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016

Attachments

cr_oracle_stored_procedures.pdf