1. Designing and Publishing Pixel-Perfect Reports in Oracle Analytics Server
  2. Model Data for Pixel-Perfect Reports
  3. Create Datasets
  4. Use Data Stored as a Character Large Object (CLOB) in a Data Model

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

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 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

    Ensure that your data doesn't include line feeds or carriage returns. Line feeds and carriage returns in your data may not render as expected in the report layouts.

To create a dataset from data stored as a CLOB:

  1. On the toolbar, click New Dataset and then select SQL Query. The New Dataset - SQL Query dialog launches.
  2. Enter a name for the dataset.
  3. If you are not using the default data source for this dataset, 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.

    For example, you could create a query in which the CLOB data is stored in a column named "DESCRIPTION".


    Description of clob_1a.gif follows
    Description of the illustration clob_1a.gif

  5. After entering the query, click OK to save. 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 xdo11g_clob_3a.gif follows
Description of the illustration xdo11g_clob_3a.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 xdo11g_clob_4a.gif follows
Description of the illustration xdo11g_clob_4a.gif

Additional Notes on Datasets 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 Add a Bursting Definition to Your Data Model with a SQL Query.

Handle XHTML Data Stored in a CLOB Column

Data from the XHTML documents stored in a database CLOB column can render the markup in the generated report.

To enable the report rendering engine to handle the markup tags, you must wrap the XHTML data in a CDATA section within the XML report data that's passed by the data engine.

It's 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 isn't 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:

Only the RTF templates support rendering of the HTML markup in a report.

Retrieve 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 clob_forhtml.gif follows
Description of the illustration clob_forhtml.gif

Wrap the XHTML Data in CDATA in the Query

This exercise assumes that you've 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.