Wednesday, February 17, 2016

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"

    1. 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
Posted by at
Labels:

2 comments:

  1. Narender SinghJune 21, 2021 at 12:21 AM

    Thanks for sharing this Information. SAP SCM Training Course in Gurgaon

    ReplyDelete
  2. SadhviNovember 5, 2024 at 11:35 PM

    This article provides a great solution to an issue many Crystal Reports users might encounter when using an ODBC connection to Oracle. The detailed explanation of why Oracle stored procedures aren't listed in Crystal Reports when using the CR Oracle ODBC Driver, and how to resolve it by unchecking the "Use Current Schema for SQLProcedures" option, is very helpful. Data science courses in Visakhapatnam

    ReplyDelete

Subscribe to: Post Comments (Atom)