SAP Knowledge Base Article - Public

1569938 - No data or incomplete data set returned in Crystal Reports when reporting of an Oracle View, Stored Procedure, or Command Object

Symptom

  • Missing records.
  • No data, or incomplete data set returned on the report.
  • Report connecting to Oracle, using a Native Oracle Connection, does not return all the records in Crystal Reports.
  • When connecting to a View, a Stored Procedure, or a Command Object, using a Native Oracle connection, it returns no data in Crystal Reports.
  • The same View, Sored Procedure, or SQL Query returns data in other applications, but in Crystal Reports returns no data, or an incomplete data set.
  • Error: "Database Error ORA-01858: a non-numeric character was found..." when refreshing a report in Crystal Reports based of a native connection to Oracle.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
      
  • Oracle 11g
  • Oracle 12c
  • Oracle 18c
  • Oracle 19c

Reproducing the Issue

  1. In Crystal Reports, create a report based on an Oracle data source using the Oracle native connection.
  2. Add an Oracle View, a Stored Procedure, or create a Command Object.
  3. When refreshing the report, notice it returns partial results, or returns no data, or returns an error message.

Cause

  • A selection on a date is hardcoded in the WHERE clause of the SQL Query of the Oracle: View, Stored Procedure, or Command Object, and the format is not supported by the Oracle Native connection. This is why it either filter out all the data based on an invalid date, or return a different data set, or failed with the error: ORA-01858

Resolution

  • When using the Oracle native driver and using a hard-coded date selection within an Oracle View, Stored Procedure or SQL Query in a command object, the date selection must use either:
          
    • A string representation format of YYYY-MM-DD (<Database Date Field> = 2016-12-31); or
               
    • Use the To_Date function with the same format specified. ( <Database Date Field> = To_Date(’2016-12-31’,’YYYY-MM-DD’)
         
  • If you do not have rights to modify the Oracle Stored Procedure or View, the solution will then be to use an ODBC Connection to Oracle instead of the Oracle Native connection. The ODBC connection does not have the requirement of a date being in a specific format.

Keywords

Oracle native connection, CR, Crystal Reports, date, DATE, Date , 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