SAP Knowledge Base Article - Public

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.
      
        
  • Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
       
  • CR Oracle ODBC Driver 6.0
  • CR Oracle ODBC Driver 7.0
  • CR Oracle ODBC Driver 7.1.5
  • CR Oracle ODBC Driver 7.1.6
         
  • CR Oracle Wire Protocol ODBC Driver 6.0
  • CR Oracle Wire Protocol ODBC Driver 7.0
  • CR Oracle Wire Protocol ODBC Driver 7.1.5
  • CR Oracle Wire Protocol ODBC Driver 8.0.2

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.
      
    No_SP.png

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 Microsoft ODBC Data Source Administrator:

      • For Crystal Reports 2011, 2013, 2016:
        • Open the 32bit ODBC Data Source Administrator.

      • For Crystal Reports 2020: 
        • Open the 64bit ODBC Data Source Administrator.
           
               
    2. Select the ODBC DSN created using the CR Oracle ODBC Driver, and click on the button "Configure"
        
      No_SP02.png
        
          
    3. In the "ODBC Oracle Driver Setup", under the tab "Performance", uncheck the option: "Use Current Schema for SQLProcedures"

      No_SP03.png   
         
       
    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 SP, StoredProc, Data Direct Oracle ODBC driver, DD ODBC , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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