Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Working with CLOB Fields

Posted by Venkatakrishnan J on November 12, 2007

I recently came across a question on an internal forum wherein the question was whether OBI EE has support for CLOBs? As you would already know, BI Publisher has native support for both CLOB and BLOB fields. Unfortunately, there is no document that states the support/de-support for CLOB on BI EE. In the example that i am going to try for testing CLOBs in BI EE, i would be using the SH schema. I have basically added one more field in my GEOGRAPHY dimension called GEOGRAPHY_DESC which is a CLOB field.

      

Lets try creating a simple report out of this CLOB field.

      

      

As you would see above this has resulted in an error. But as you would see above this has resulted in an OCI specific error and not an OBI EE error. So, i thought of taking a look at the actual SQL that was getting fired in the backend.

      

As you see, the SQL fired actually had a distinct clause which is not supported for CLOB fields. Even GROUP BYs are not supported for CLOBs (You can test this SQL plus or SQL Developer).

      

Since the query generated was because of the repository design(the geography was a dimension and hence the distinct clause got added), i thought i would give it a try using direct database requests.

      

      

Looks like CLOBs are indeed not supported directly since even in a direct database request we do not get the actual data. We only get some junk characters. So, the only way as of now is to use the DBMS_LOB package to break the CLOB into small chunks and then use them in the reports. For example, you can directly use the EVALUATE and DBMS_LOB.SUBSTR on the CLOB field to get the data.

      

      

So till we get actual support use the DBMS_LOB package.

Advertisements

4 Responses to “Oracle BI EE 10.1.3.3/2 – Working with CLOB Fields”

  1. Vivek said

    It followed the steps given above but it is giving the following error.
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46035] Datatype(VARCHAR) nullable(1) unicode(1) external char(0) conversion to datatype(LONG VARBINARY) nullable(1) unicode(0) external char(0) is not supported. (HY000)
    SQL Issued: SELECT EVALUATE(‘DBMS_LOB.substr(%1,40,1)’,”HPQC_DEFECT”.”BG_DESCRIPTION”) saw_0 FROM Deffect ORDER BY saw_0

    when imported in OBIEE Admin Tool the Clob field is converted to Longvarchar. Can you suggest how do I correct this.

  2. Nir said

    Hi 🙂
    first of all, thanks for this info.
    in my case i had a BLOB field, so I made a function that translates it to CLOB.
    than i’ve made a DB View that holds my function results.

    than I made what you said and the field was the field inside my view.

    EVALUATE(‘DBMS_LOB.SUBSTR(%1,40,1)’,PACKET_CONTENT_TEXT)

    the result I got is only one char (which change if i change the 40 to any other number)… but thats it, it doesnt show all the blob or part of it.

    any Ideas ?
    thanks 🙂

  3. VOIDUNRAJ said

    CREATE A NEW LOGICAL FIELD BY DOING A CAST OF THE CLOB FIELD TO A VARCHAR(LENGTH) AND USE THIS IN THE PRESENTATION.
    THIS CAN SOLVE THE ISSUE.

  4. Abdul said

    As a work around, can we split the CLOB values to fit into varchar, and concat them in OBIEE reports. I have not tried this yet. Still can give a try.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: