Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Oracle CLOB Column in HTML Format (Contains HTML Tags) - How to Handle?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Oracle CLOB Column in HTML Format (Contains HTML Tags) - How to Handle?
 Login/Join
 
David Briars
Master
posted
I am reading an Oracle table that has a CLOB column.

The CLOB column contains text data, the text data is narrative data surrounded by HTML tags, e.g., FONT, DIV,...

The FIELD definition in the master looks like this:
FIELDNAME=MYCLOB, ALIAS=MYCLOB, USAGE=TX50, ACTUAL=TX,
      MISSING=ON, $

In order to display the data in a WebFOCUS HTML report I have the following:
DEFINE FILE MYORAPROC
  NEWMYCLOB/A32000V = MYCLOB;
END
TABLE FILE MYORAPROC
  PRINT
   ACOLUMN
   NEWMYCLOB
   BCOLUMN
END

The report looks fantastic...both the data from the CLOB column, and the other columns in table look great.

I have now found out that the data with the CLOB could be more that 32000 positions long.

Any ideas on how I can display all the data in the CLOB, in my HTML report?

My researching this issue has been challenging.

(Just PRINTing the CLOB column, MYCLOB in this case, renders improperly, probably because the HTML tags are breaking.)

This message has been edited. Last edited by: <Emily McAllister>,
 
Posts: 822 | Registered: April 23, 2003Report This Post
eric.woerle
Master
posted Hide Post
David,

My only suggestion would be to create virtual fields in the database that would break up the clob every 32,000 characters. Although you need to make sure that in doing so you don't break up a word or a tag.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
David Briars
Master
posted Hide Post
Thank you so much for the possible option, Eric.

I have accordingly asked my Oracle team if this is possible.

Say, I can get three Oracle columns, each at 32000, how would I use them in a report?

If I say...
...
PRINT BIGCOL1
      BIGCOL2
      BIGCOL3
...

...I think WF would try to display each of the components in separate columns.

How would I combine?
 
Posts: 822 | Registered: April 23, 2003Report This Post
dbeagan
Platinum Member
posted Hide Post
Would something like this work (REGION ST and CITY would be the CLOB fields)?
 SET PAGE=NOPAGE
 TABLE FILE GGSALES
 PRINT REGION AS ''
  OVER ST     AS ''
  OVER CITY   AS ''
 WHERE SEQ EQ 1
 ON TABLE HOLD FORMAT WP
 END
-RUN
-HTMLFORM BEGIN
!IBI.FIL.HOLD;
-HTMLFORM END


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
David Briars
Master
posted Hide Post
Nice design pattern dbeagan!

This will give me something to play with, for sure.

Thanks!
 
Posts: 822 | Registered: April 23, 2003Report This Post
eric.woerle
Master
posted Hide Post
What dbeagan said. lol

Thats what I would have tried. If that doesn't work you can consider writing each field to a html file with PUTDDREC and using -HTMLFORM INCLUDE.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Wep5622
Virtuoso
posted Hide Post
I can't help but notice that your example doesn't use SET HTMLENCODE ON. Perhaps that's (part of) what you're looking for?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
David Briars
Master
posted Hide Post
@Eric: I forget about the function PUTDDREC. Yes, I can see how that would be something to try. Thanks.

@Wep: Yes, I've been turning HTMLENCODE ON to double-check/view the HTML that is coming out of the CLOB. It has been useful. Then I turn it off, when I want the HTML from the CLOB to render. Thanks.

Update: It looks like we might be able to get agreement, at our site, that the Oracle table CLOB not contain more than 32K. I'm hoping! :-)

This message has been edited. Last edited by: David Briars,
 
Posts: 822 | Registered: April 23, 2003Report This Post
Wep5622
Virtuoso
posted Hide Post
IMO, it's fine if the field is over 32k in length, but in the report I would provide the column with its contents truncated to a reasonable length and then provide a drill down from there to a separate report for displaying the full contents of the field.

The benefit: For a single field value you are not limited to a single line/row to display the fields' contents, so you don't need to jump through as many hoops to get the output.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
David Briars
Master
posted Hide Post
quote:
...provide the column with its contents truncated to a reasonable length and then provide a drill down from there to a separate report for displaying the full contents of the field.

Yep, this is a 'best practice' and a good UX.

We've used this approach before, but in this particular case, the requirement was to incorporate all data, within the report.

Many thanks Wep, and all, as always, for your thoughts.
 
Posts: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Oracle CLOB Column in HTML Format (Contains HTML Tags) - How to Handle?

Copyright © 1996-2020 Information Builders