Requirements to access Oracle stored procedures from CR
The following requirements must be met in order for CR to access an Oracle
stored procedure:

1. 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.
NOTE Crystal Reports 9 native connections will support Oracle stored procedures created within
packages as well as Oracle stored procedures referencing weakly bound REF
CURSORs.
Crystal Reports 8.5 native connections will support Oracle stored procedures referencing
weakly bound REF CURSORs.

2. The procedure must have a parameter that is a REF CURSOR type. This is
because CR uses this parameter to access and define the result set that the
stored procedure returns.

3. The REF CURSOR parameter must be defined as IN OUT (read/write
mode). After the procedure has opened and assigned a query to the REF
CURSOR, CR will perform a FETCH call for every row from the query's
result. This is why the parameter must be defined as IN OUT.

4. Parameters can only be input (IN) parameters. CR is not designed to work
with OUT parameters.

5. The REF CURSOR variable must be opened and assigned its query within
the procedure.

6. The stored procedure can only return one record set. The structure of this
record set must not change, based on parameters.

7. The stored procedure cannot call another stored procedure.

8. If using an ODBC driver, it must be the CR Oracle ODBC driver (installed
by CR). Other Oracle ODBC drivers (installed by Microsoft or Oracle)
may not function correctly.

9. If you are using the CR ODBC driver, you must ensure that in the ODBC
Driver Configuration setup, under the Advanced Tab, the option 'Procedure
Return Results' is checked ON.

10. If you are using the native Oracle driver and using hard-coded date selection
within the procedure, the date selection must use either a string
representation format of 'YYYY-DD-MM' (i.e. WHERE DATEFIELD =
'1999-01-01') or the TO_DATE function with the same format specified
(i.e. WHERE DATEFIELD = TO_DATE ('1999-01-01','YYYY-MM-DD').
For more information, refer to kbase article C2008023.

11. Most importantly, this stored procedure must execute successfully in
Oracle's SQL*Plus utility.
If all of these conditions are met, you must next ensure you are using the
appropriate database driver. Please refer to the sections in this white paper for a
list of acceptable database drivers.