Using Data Stored as a Character Large Object (CLOB) in a Data Model

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:

  1. On the toolbar, click New Data Set and then select SQL Query. The New Data Set - SQL Query dialog launches.
  2. Enter a name for the data set.
  3. If you are not using the default data source for this data set, select the Data Source from the list.
  4. Enter the SQL query or use the Query Builder to construct your query to retrieve the CLOB data column. See Using the SQL Query Builder for information on the Query Builder utility. For example, you could create a query in which the CLOB data is stored in a column named "DESCRIPTION".
  5. After entering the query, click OK to save. BI Publisher validates the query.
  6. By default, the data model editor assigns the CLOB column the "CLOB" data type. To change the data type to XML, click the data type icon and select XML.

How the Data Is Returned

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.


Description of GUID-C3B2E729-2F9B-4EB4-A2B7-EF68EB014383-default.gif follows
Description of the illustration GUID-C3B2E729-2F9B-4EB4-A2B7-EF68EB014383-default.gif

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.


Description of GUID-9109627D-BEF6-4C5A-9B11-A843F921542E-default.gif follows
Description of the illustration GUID-9109627D-BEF6-4C5A-9B11-A843F921542E-default.gif

Additional Notes on Data Sets Using CLOB Column Data

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.

Handling XHTML Data Stored in a CLOB Column

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.

Retrieving XHTML Data Wrapped in CDATA

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.

Description of GUID-0BFD2D9C-8A8F-4A8C-8EF3-1E8083D975D2-default.gif follows
Description of the illustration GUID-0BFD2D9C-8A8F-4A8C-8EF3-1E8083D975D2-default.gif

Wrapping the XHTML Data in CDATA in the Query

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.