Skip to Content
author's profile photo Former Member
Former Member

CR 2008 - Oracle 11g - CLOB fields are blank when using command

We have recently upgraded our backend DB to Oracle 11gR1 and several reports have blank fields now.

After looking at the reports I found that the blank fields are Oracle CLOB fields, and the report is using a command via an Oracle native connection.

If I pull in the CLOB fields directly from the tables, they appear fine in the report.

CR 2008 SP3

Oracle Server 11gR1

Oracle Client 10gR2 and 11gR1

Has anyone else experienced this issue?

Does anyone have any suggestions?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 17, 2010 at 03:45 PM

    How can this possibly be a size limit issue?

    Are you saying that the size limit if a use the Oracle 10g client is larger than if I use the 11g client?

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Don,

      I can't say if it's an Oracle issue but for CR the Kbase article indicates:

      Symptom

      When retrieving data from a CLOB field in an Oracle database, after generating approximately 28 pages, the data gets truncated.

      Cause

      When a CLOB field is mapped in to Crystal Reports, it is treated as a Memo field. Memo fields have a 64 KB size limit. If the field contains more data, the remaining data will not be displayed in Crystal Reports.

      Resolution

      To work around this issue, use a stored procedure to split the field into 64KB segments.

      So it may be a Command Object limitation due to SQL buffering within CR SQL query engine. If you want this looked at by the developers then log a case on line, if it is a bug then you'll get a refund.

      Thank you

      Don

  • Posted on Oct 01, 2010 at 07:48 PM

    Hi Donald,

    I've found that having more than one Oracle client on the same PC doesn't always work for CR. CR still uses the PATH statement to find the client engine. Try setting the PATH so 11g is the first reference and if possible remove the 10 from the PATH also. YOu may even have to un-install both, clean up after Oracle and then install just the 11g client.

    And to confirm, you are using the CR Native Oracle Driver or are you using ODBC or OLE DB?

    Thank you

    Don

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi don,

      Apparently this is a known limitation. Has to do with the size of the buffer and the size of the clob data. Smaller ones may work.

      Other options are to convert your Command Object into a Stored Procedure or View and then report off the DB directly. Or add your Clob in a subreport and link it to the main report.

      Search Kbase and you'll find a few articles on there limitations:

      1204190 - Size limitation of an Oracle CLOB field in Crystal Reports

      Thank you

      Don

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.