CR 10 cannot display Oracle BLOB

[hawkseyesys]

Tech Info:
Crystal Reports 10
Oracle 9.2.06
Connectivity: native Oracle 9.0.1.1.1

I've read through all the posts regarding this issue but haven't found a solution. I am attempting to place a BLOB field from an ERP system into a Crystal Report.

I have a main report which includes a formula which provides the link to the table where the BLOB resides. That table is in a subreport. I know I'm linking to the correct record because I can display other fields from that record on the subreport. But when I place the BLOB field, nothing displays.

I've checked for LENGTH of the BLOB field and it has data in it. It is text data, according to another descriptive field in the record.

Based on other threads I have attempted to create a SQL expression to convert the BLOB to a VARCHAR2.

CONVERT(<blob_field>,varchar2(1000))
results in the ORA-00936: missing expression error.

CAST(<blob_field> AS varchar2(1000))
results in the ORA-00932: inconsistent datatypes: expected NUMBER got BLOB error.

SUBSTR(<blob_field>,1,500)
results in that same ORA-00932 error.

Another post from the ERP website suggested

Change the Paragraph Formatting of the field so that "Text Interpretation" is set to "RTF Text".
However, Paragraph Formatting does not appear to be an option for this type of field.

The solution to this problem is critical to our implementation of the ERP and any suggestions would be much appreciated.

 

[synapsevampire]

Right, you've done everything I wouold have tried, and your understanding is correct.

However we need you to post what you actually tried, not <blob_field>, there's no advantage to your not doing so,but disadvantages in your not being accurate with precisely what was tried.

And what data type does Crystal report it as?

The ERP poster was suspicious tht it contained formatted text, whereas I'm suspicious that it contains a file within, which is fairlycommon with BLOBs.

So you think it's text, when in fact it might be an RTF file or some such.

Anyway, somethings to think about...

-k

 

[hawkseyesys]

Thanks, synapsevampire, for your response. I'm glad I seem to be on the right track, anyway.

This report uses data from J D Edwards Enterprise One v8.11. The actual formulas I posted use this field:

CAST("F00165"."GDTXFT",varchar2(1000))

In the CR Field Explorer, this field is identified as a Picture. And when I right-click on it to format it, the option is Format Graphic, not Format Field.

I believe you are correct that although it's tagged as 'Text1' in the GDGTITNM field, it is actually RTF. But in that case, shouldn't it just appear on the report as if it were a picture?

I appreciate your help.

 

[2juvenile]

I'm having the same problem in Crystal XI and haven't been able to find a solution. If you figure it out, would you please let me know?

 

[synapsevampire]

Start a new thread and post specifics...

-k