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

SAP Knowledge Base Article - Public

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

Symptom

  • Incorrect data set returned.
  • No data or incomplete data set returned on the report.
  • When reporting natively off an Oracle data source using: a view, a stored procedure, or a command object, it returns no data, or returns a partial result in Crystal Reports.
  • The Oracle View, the Oracle Stored Procedure, or SQL Query returns data in other applications, but 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 off a native connection to Oracle.

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
      
  • Oracle 10
  • Oracle 11g
  • Oracle 12c

Reproducing the Issue

  1. In Crystal Reports, create a report off 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

  • There is a date selection in the Oracle View, stored procedure, or SQL Query used in the Command Object and the format of the date value is not supported by this type of connectivity.

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-DD-MM (where the date field = 2011-01-01); or
               
    • Use the To_Date formula function with the same format specified. (where date field = To_Date(’2011-01-01’,’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 BusinessViews Manager , Problem

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports XI R2