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

• 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]"
    
      
    
   
     
• 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.  
    

• SAP Crystal Reports 2008
• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
            
• Oracle 12c

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]"

• 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

• The issue is resolved in the following product updates:
         
• Crystal Reports 2008:
• Service Pack 7 - Fix Pack 3
       
• Crystal Reports 2011:
• Support Pack 11 - Patch 9
• Support Pack 12 - Patch 2
      
• Crystal Reports 2013:
• Support Pack 04 - Patch 9
• Support Pack 05 - Patch 5
• Support Pack 06 and higher
        
               
     
• Note: For Crystal Reports 2011, if you cannot apply a product update for some time, then to workaround this issue, 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.

Crystal Report 2008, Crystal Report 2011, Crystal Report 2013, Oracle 12c, Stored Procedure , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013