SAP Knowledge Base Article - Public

2322011 - Error: 'ORA-24374: define not done before fetch or execute and fetch' when refreshing a report in Crystal Reports

Symptom

  • Fail to refresh a report.
  • When adding a SQL Query that uses comments to a report, it fails. 
  • Existing reports used to refresh successfully before updating the Oracle Client.
  • When refreshing or creating a report connected to Oracle in Crystal Reports, that is based on a Command Object with a SQL Query that contains comments on multiple lines, it fails with the error:
          
        "Failed to retreive data from database.
         Details: HY000:[Oracle][ODBC][Ora]ORA-24374: define not done before fetch or execute and fetch
         [Database Vendor Code: 24374 ]"
         
      
        Oracle ODBC Error.png

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
            
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2
          
  • Oracle 12c

Reproducing the Issue

  1. Install the Oracle 12c Client.
  2. In Microsoft ODBC Administrator, create an ODBC DSN to an Oracle database using the Oracle 12 ODBC Driver.
  3. In Crystal Reports, create a new report to the Oracle database using the ODBC connection.
  4. Add a Command Object in which you add your SQL Query with comments displaying on multiple lines like:
       
      /* This is a comment
           displayed on multiple lines */
         
      Select Name, Country
      From Customer
      Where Country = 'USA'
          
  5. When clicking "OK", it fails with the error:
         
      "Failed to retreive data from database.
       Details: HY000:[Oracle][ODBC][Ora]ORA-24374: define not done before fetch or execute and fetch
       [Database Vendor Code: 24374 ]"

Cause

  • The error occurs when:
    • The report is based on a SQL Query that contains comments on multiple lines that start with the charcaters: ' /*'  and end with the characters:  '*/'
    • The report connects to an Oracle data source using the Oracle 12 ODBC Driver.
        
  • This is an issue with the Oracle 12 ODBC driver not accepting comments on multiple lines when using the comments characters.
           
  • This issue can be reproduce with any application connecting to Oracle using the Oracle 12 ODBC driver that uses comments on multiple lines.

Resolution

  • To workaround the Oracle 12 ODBC driver issue, perform one of the following:
    • Remove the multiple lines comments used in the SQL Query of the report;  or
    • Add at the beginning of each comment line of the SQL Query used on the report, the comment characters: --, instead of: /* */;  or
    • Use the CR Oracle ODBC Driver that install with Crystal Reports, instead of using the Oracle 12 ODBC Driver; or
    • Use the Oracle Native connection, instead of using an ODBC connection.

Keywords

CR, Oracle , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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