SAP Knowledge Base Article - Public

2915018 - Crystal Reports crash when creating, or refreshing a report based of a Command Object that uses the Oracle function: listagg

Symptom

  • Crystal Reports crash.
  • When reporting of Oracle, and using a custom SQL Query, Crystal Reports crash.
  • When refreshing a report based on Oracle, containing a field with large amount of text, Crystal Reports crash.
  • When creating, or refreshing a report based on an Oracle Native connection, with a Command Object that uses the Oracle function: listagg, or that uses a field containing a large amount of text, Crystal Reports crash.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
       
  • Oracle 12c 
  • Oracle 18c
  • Oracle 19c

Reproducing the Issue

  1. In Crystal Reports, create a new report using an Oracle native connection.
       
  2. Add a Command Object, and enter a SQL Query that uses the Oracle function: listagg, like:
      
        SELECT Field_01, 
                    Field_02,
                    Listagg(Field_01,',') WITHIN GROUP (Field_02) As Field_03
        FROM Table_01
        GROUP BY Field_02
      
  3. When refreshing the report, Crystal Reports crash.

Cause

  • This issue only occur when:
    • Using an Oracle Native connection; and
    • Using a Command Object with a custom SQL Query that uses the Oracle function: listagg; or
    • When adding to the report a fields containing a large amount of text; and
    • The Oracle server Parameter: MAX_STRING_SIZE is set to: EXTENDED, which increase the size of VARCHAR2 expressions from 4,000 to 32,767 
                        
  • A product enhancement has been tracked under SAP Note 2916885, to add support to the new Oracle Parameter: MAX_STRING_SIZE when set to: EXTENDED

Resolution

  • The issue is resolved in the following product update, and above:
          
    • Crystal Reports 2016:
      • Support Pack 07 - Patch 11
      • Support Pack 08 - Patch 3     
         
             
  • If you cannot apply the update at this point, then to workaround the issue, perform one of the following:
          
    • Use an ODBC connection using the Oracle ODBC driver installed with the Oracle client matching the version of Oracle you are connecting to; or 
           
    • Edit the SQL Query, and use the function: substr to limit the Oracle function: listagg to 4,000 characters. For example, the SQL Query will look like:   
        
          SELECT Field_01, 
                      Field_02,
                      Substr(Listagg(Field_01,',') WITHIN GROUP (Field_02),1,4000) As Field_03
          FROM Table_01
          GROUP BY Field_02

Keywords

ORA, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016