Using Reports to Print CLOBs

thatjeffsmith SQL Developer 0 Comments

Tell Others About This Story:

A co-worker asked me for some advice on building a report to view some CLOBs.

CLOBs are fun. They let you store an unlimited (basically) amount of text. They’re not as flexible as VARCHAR2s, but they do come in handy.

Viewing them in a report can get interesting though.

The question came down to seeing a report like this –

Get some of the CLOB text by default, then do more work to get the rest.

Get some of the CLOB text by default, then do more work to get the rest.

I suggested to my co-worker to use the SCRIPT type report instead.

But then they noticed they only got the first 80 chars.

Well, that’s easily fixed.

SET LONG 4000 [DOCS] — we default to 80 chars…

Better, but is this the best we can do?

Better, but is this the best we can do?

WE CAN DO BETTER

So let’s use a trick.

Instead of using a child report of type SCRIPT, we’re going to use a DBMS_OUTPUT report. We’re going to print HTML tags, and make the CLOB look pretty. Or more pretty. Or prettier?

Ta-da!

Ta-da!

What the child report looks like.

I'm selecting the CLOB into a CLOB and then printing THAT using DBMS_OUTPUT, surrounded by HTML tags.

I’m selecting the CLOB into a CLOB and then printing THAT using DBMS_OUTPUT, surrounded by HTML tags.

Remember, the REPORTS feature is awesome. You have a few different ways to get what you want. We can render basic HTML tags. A few minutes of work will save you hours of time and tons of eye strain and repetitive stress injuries down the road. And lastly, you can SHARE your reports very easily.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *