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 ]"
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
- Install the Oracle 12c Client.
- In Microsoft ODBC Administrator, create an ODBC DSN to an Oracle database using the Oracle 12 ODBC Driver.
- In Crystal Reports, create a new report to the Oracle database using the ODBC connection.
- 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'
- 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