Wednesday, February 17, 2016

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

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
  • Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2016
         
  • Oracle 12c

Reproducing the Issue
  1. 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;
  2. 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 )
       
  3. 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
  • The issue is resolved in the following product updates:
           
    • Crystal Reports 2008:
      • Service Pack 7 - Fix Pack 3
          
    • Crystal Reports 2013:
      • Support Pack 04 - Patch 9
      • Support Pack 05 - Patch 5
      • Support Pack 06 and higher
              
                     
           
  • 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.
            
    1. Confirm you are using Crystal Reports 2011 with Support Pack 09 or higher, as Oracle 12c support started in SP09.
              
    2. 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.
         
    3. 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.
              
    4. 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.

See Also

Keywords
Crystal Report 2008, Crystal Report 2011, Crystal Report 2013, Oracle 12c, Stored Procedure


Header Data
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

No comments:

Post a Comment