Helping Notes: 2140646 - Error: "Failed to retrieve data from the database. Details: ORA-00942" when attempting to create a report off an Oracle 12c Stored Procedure in Crystal Reports

Wednesday, February 17, 2016

Symptom

Error: "Failed to retrieve data from the database. Details: ORA-00942: table or view does not exist [Database Vendor Code:942]"
When attempting to report off some Oracle 12c Stored Procedure, it fails.
Can successfully report off the same Stored Procedure on Oracle 11g.
When attempting to report off Oracle 12c Stored Procedure defined in a package, using the Oracle Native connection in Crystal Reports, it fails with the error:

  "Failed to retrieve data from the database. Details: ORA-00942: table or view does not exist [Database Vendor Code:942]"

Followed by the error:

  "Failed to retrieve data from the database. Details: Fail to execute SQL statement. OCI Call: OCIStmtExecute [Database Vendor Code: 942]"

Environment

Reproducing the Issue

In Oracle 12c, create an Oracle Stored Procedure defined in a Package, like:
          Create or Replace PACKAGE Test_Package
          AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
          PROCEDURE Test_Procedure (Test_Cursor IN OUT Test_Type);
          END Test_Package;

          Create or Replace PACKAGE BODY Test_Package
          AS PROCEDURE Test_Procedure (Test_Cursor IN OUT Test_Type) IS
             BEGIN
                OPEN Test_Cursor FOR
                SELECT *
                FROM Test_Table;
             END Test_Procedure;
          END Test_Package;
In Crystal Reports, create a new report, and connect to an Oracle 12c data source using the Oracle Server connection. ( Often refer to as the Oracle native connection )
When attempting to add the Oracle Stored Procedure defined in a Package to the report, it fails with the error:

"Failed to retrieve data from the database. Details: ORA-00942: table or view does not exist [Database Vendor Code:942]"

Cause

The issue only occurs when attempting to add an Oracle 12c Stored Procedure defined in a Package.
The reason it occurs, it's due to a change in how Oracle works, and the order of commands sent by Crystal Reports to retreive the Oracle Stored Procedure information.
This issue has been tracked under SAP Note 2140661

Resolution

For Crystal Reports 2011, there is no product update that resolve this issue, but to workaround it, copy the Oracle Native driver ( crdb_oracle.dll ) from Crystal Reports 2013 with Support Pack 06 to Crystal Reports 2011.

Confirm you are using Crystal Reports 2011 with Support Pack 09 or higher, as Oracle 12c support started in SP09.
Download the Oracle Native driver for Crystal Reports 2013 Support Pack 06, which can be found at the bottom of this SAP Knowldge Base Article in the attachments section.
Copy the Oracle Native driver:
    - crdb_oracle.dll
    - crdb_oracle_res_en.dll

To the following directory.

     C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86

Note: We recommend make a backup copy of the existing crdb_oracle.dll first.
Restart Crystal Reports 2011, and you will then be able to create a report off an Oracle 12c Stored Procedure defined in a Package.

Important Note: This is a workaround only, there is no guarentee it will not introduce other issues when connecting to an Oracle data source using the Oracle Native driver. The best solution will be to use a version of Crystal Reports where the issue has been resolved, which is Crystal Reports 2013 with Support Pack 06 or higher.


Released On	23.12.2015 19:44:08
Release Status	Released to Customer
Component	BI-RA-CR Crystal Reports designer or BusinessViews Manager
Priority	Normal
Category	Problem

Helping Notes