Crystal Reports XI BLOB text data

[2juvenile]

I am working with an Oracle database that contains a BLOB with text only data. When I insert this field into a Crystal Reports XI report, the data does not show up. I am sure the text is there. Attempts to convert the BLOB to text by creating a formula field (To_Text({GSP_BOOKING.NOTES}) give an error message that BLOBS cannot be used in a formula. How do I get this text to show up in the report?

 

[synapsevampire]

How are you sure that the text is there?

Copy the query from Database Show SQL Query and paste it into your Oracle query tool of choice, does it show this text data? If not, then you aree mistaken abut how it is stored.

It could be a text file, it coudl be compressed, encrypted, etc.

In general, can you query the field fromj a SQL tool and get text data returned from that field?

What form of connectivity are you using to the database? ODBC, Oracle Server5 (native, or?

-k

 

[2juvenile]

I verified that the data was present with PL/SQL tool. I can see it using a simple query. I'm working with a Concept database and they identify the field as a BLOB. Crystal shows it as a Picture type. Connectivity is an Oracle Server.

 

[synapsevampire]

Take the same query that you verified in PL/SQL and pastye it into the Add Command listed under the datasource in Crystal.

This is how you set up SQL to be the data source for a report.

Then check what is being generated by Crystal in your table version using the Database->Show SQL Query (I alluded to this before).

-k

 

[2juvenile]

I did throw the SQL query from my Crystal Report into PL/SQL Developer and the BLOB field shows up, but it just has <BLOB> for data with a small button on the right that, if pressed, gives you the data. You lost me on the Add Command. Sorry.

 

[synapsevampire]

Go into Database->Database Expert

Below your datasource you'll see Add COmmand.

USe whatevcer SQL you used successfully in PL/SQL in there as the source for the report.

So what is the difference between what Crystal develops as SQL and what you used successfuly, that was the point of my mentioning it.

-k

 

[2juvenile]

Thanks for the instructions about Add Command. That will undoubtedly be useful. I don't see any difference in Crystal's SQL. In Add Command, I used

SELECT NOTES
FROM GSP_BOOKING_LOG

Crystal's SQL refers to it as

SELECT "GSP_BOOKING_LOG"."NOTES"
FROM "GSP_BOOKING_LOG"

... and I couldn't see the notes either way.

 

[synapsevampire]

If you're using Oracle Server as the connectivity, that's generally the best (that's a a native connection for future reference).

But you might try using the Oracle provided ODBC driver connectivity and see if that works.

PL/SQL Developer\ doesn't use either, and has some explicit conversion embedded within.

I assume that you used:

"SELECT NOTES
FROM GSP_BOOKING_LOG"

in PL/SQL developer as well.

-k

 

[2juvenile]

Yes. I cut and pasted the code from PL/SQL to Crystal Add Command.

 

[synapsevampire]

<nodding>

Sounds like you need to try other forms of connectivity, and learn how to extract the data w/o using PL/SQL dev.

-k

 

[2juvenile]

I appreciate all you time. Thanks so much.