1980814 - No Oracle stored procedures listed in Crystal Reports, when using an ODBC connection

Symptom

• Stored procedures missing.
• No Oracle stored procedures listed in Crystal Reports, when attempting to create a new report via an ODBC connection to Oracle using the CR Oracle ODBC driver. 
• When creating a new report via an Oracle native connection, it list all the Oracle stored procedures.

Environment

• Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
     
• CR Oracle ODBC Driver 5.3
• CR Oracle ODBC Driver 6.0
• CR Oracle Wire Protocol ODBC Driver 5.3
• CR Oracle Wire Protocol ODBC Driver 6.0

Reproducing the Issue

1. Install and configure the Oracle client on the local computer.
2. In the ODBC Data Source Administrator, create an ODBC DSN to the Oracle database using the CR Oracle ODBC Driver.
3. In Crystal Reports, when creating a native Oracle connection to the database, it list all the Oracle stored procedures.
4. But when creating an ODBC connection to the same Oracle database using the ODBC DSN created in step 2, it does not list any Oracle stored procedure.
   

Cause

• The ODBC DSN created to the Oracle database is configured to only return the stored procedures owned by the user logged in to Oracle.
• Since the user logged in to Oracle isn't the owner of any of the stored procedures, it does not list any stored procedures.

Resolution

• To display a list of all available Oracle stored procedure when using the following ODBC drivers to connect to Oracle:
      
     - CR Oracle ODBC Driver
     - CR Oracle Wire Protocol ODBC Driver
  
  Uncheck the CR Oracle ODBC driver option: "Use Current Schema for SQLProcedures". To do so, follow the steps below:
          






1. Open the ODBC Data Source Administrator.
2. Select the ODBC DSN created using the CR Oracle ODBC Driver, and click on the button "Configure"
   
   
3. In the "ODBC Oracle Driver Setup", under the tab "Performance", uncheck the option: "Use Current Schema for SQLProcedures"
   
   

4. Click "OK" to accept the change.
     
   Now, when creating a new report in Crystal Reports using the CR Oracle ODBC driver, it will list all the available Oracle Stored Procedures.

Keywords

CR, Oracle, Data Direct Oracle ODBC driver, DD ODBC

Header Data

Released On	13.03.2014 16:10:12
Release Status	Released to Customer
Component	BI-RA-CR Crystal Reports designer or BusinessViews Manager
Priority	Normal
Category	Problem