BI Publisher supports using data stored as a character large object (CLOB) data type in your data models. This feature enables you to use XML data generated by a separate process and stored in your database as input to a BI Publisher data model.
Use the Query Builder to retrieve the column in your SQL query, then use the data model editor to specify how you want the data structured. When the data model is executed, the data engine can structure the data either as:
A plain character set within an XML tag name that can be displayed in a report (for example, an Item Description)
Structured XML
Note:
Ensure that your data does not include line feeds or carriage returns. Line feeds and carriage returns in your data may not render as expected in BI Publisher report layouts.
To create a data set from data stored as a CLOB:
When you execute the query, if the CLOB column contains well-formed XML, and you select the XML data type, the data engine returns the XML data, structured within the CLOB column tag name.
Example output when data type is XML:
Note the <DESCRIPTION> element contains the XML data stored in the CLOB column, as shown below.
Example output when data type is CLOB:
If you select to return the data as the CLOB data type, the returned data is structured as shown below.
More information is available on CLOB column data.
For specific notes on using CLOB column data in a bursting query, see Adding a Bursting Definition to Your Data Model.
BI Publisher can retrieve data stored in the form of XHTML documents stored in a database CLOB column and render the markup in the generated report.
To enable the BI Publisher report rendering engine to handle the markup tags, you must wrap the XHTML data in a CDATA section within the XML report data that is passed by the data engine.
It is recommended that you store the data in the database wrapped with the CDATA section. You can then use a simple select statement to extract the data. If the data is not wrapped in the CDATA section, then you must include in your SQL statement instructions to wrap it.
The following sections describe how to extract XHTML data in each case:
To display the markup in a report, you must use the syntax described in Rendering HTML Formatted Data in a Report in Report Designer's Guide for Oracle Business Intelligence Publisher. This section also describes the supported HTML formats. Rendering the HTML markup in a report is supported for RTF templates only.
This exercise assumes you have the following data stored in a database column called "CLOB_DATA".
<![CDATA[ <p><font style="font-style: italic; font-weight: bold;" size="3"> <a href="http://www.oracle.com">oracle</a></font> </p> <p><font size="6"><a href="http://docs.oracle.com/">Oracle Documentation</a> </font></p> ]]>
Retrieve the column data using a simple SQL statement, for example:
select CLOB_DATA as "RTECODE" from MYTABLE
In the data model editor, set the data type of the RTECODE column to XML, as shown below.
This exercise assumes you have the following data stored in a database column called "CLOB_DATA".
<p><font style="font-style: italic; font-weight: bold;" size="3"> <a href="http://www.oracle.com">oracle</a></font> </p> <p><font size="6"><a href="http://docs.oracle.com/">Oracle Documentation</a> </font></p>
Use the following syntax in your SQL query to retrieve it and wrap it in the CDATA section:
select '<![CDATA' || '['|| CLOB_DATA || ']' || ']>' as "RTECODE"from MYTABLE
In the data model editor, set the data type of the RTECODE column to XML.