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                     

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