1181947 - Oracle Stored Procedure requirements for creating reports in Crystal Reports

• Is it possible to report of 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?

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020
      
• Oracle 11g
• Oracle 12c
• Oracle 18c
• Oracle 19c

• In order for Crystal Reports to report of 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 an Oracle native connection, it is possible to report off the Oracle stored procedure that 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 Oracle ODBC driver, check the option: "Procedure Returns Results" under the tab "Advanced" in the CR Oracle ODBC DSN
    
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 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.pdf
    
  Found in the Attachment section below. Note that the document was created for older versions of Crystal Reports, but the information it contains is still valid for the latest versions of Crystal Reports.

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

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020