Symptom
- Error: 'ORA-00900: invalid SQL statement'
- Error when attempting to create a report based on a custom SQL Query.
- When creating a report based on Oracle, and adding a Command Object with a custom SQL Query, Crystal Reports fails with the error:
"Failed to retrieve data from the database.
Details: ORA-00900: invalid SQL statement
[Database Vendor Code: 900 ]"
- 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.
Environment
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
- Oracle 12c
- Oracle 18c
- Oracle 19c
Reproducing the Issue
- In Crystal Reports, create a new report based on Oracle.
- In the Database Expert, double click on: "Add Command"
- In "Add Command To Report", type the SQL Query, like:
{ SELECT Country
FROM Customer
WHERE Country = 'USA' } - When clicking "OK", it generates the error:
"ORA-00900: invalid SQL statement"
Cause
- It is an Oracle error, indicating an incorrect SQL Query syntax.
- The error occurs because the SQL Query starts and ends with french brackets: { }, which is an incorrect syntax.
Resolution
- Remove the french brackets at the start and end of the custom SQL Query.
For example: If the SQL Query is:
{ SELECT Country
FROM Customer
WHERE Country = 'USA' }
Then the solution is to remove the french brackets: { }, from at the start, and the end of the SQL Query, like:
SELECT Country
FROM Customer
WHERE Country = 'USA'
Keywords
CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020