Varchar2 greater than 4000 in OBIEE

20 Apr

I came across one requirement where one column having approximately 7000 char length when we trying to convert that CLOB field into varchar.But as we know oracle has limitation on varchar2 length upto 4000 chars only.

So the workaround I have approached is:

Split the 7000 char clob column into two varchar columns in RPD using DBMS_LOB.substr() function

Syntax for DBMS_LOB.substr() is:

DBMS_LOB.substr(<table.columnname>,<length of characters>,<start position>)

Column1: This brings first 3500 chars

CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,1)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))

Column2:  This brings Next 3500 chars

CAST ( EVALUATE(‘DBMS_LOB.substr(%1,3500,3501)’, Customers.”Customer Segment”) AS VARCHAR ( 3500 ))

Now in Answers drag these two columns into criteria and one more column:

In 3rd column-> go to column properties-> data format and select Treat Text AsCustom Text Format.

And in Custom Text Format give the text liek this: @[html]@1@2


@1 and @2 are column positions in answers criteria.

In this case BI server doesn’t include the 3rd column in query to DB as we are Camouflage the concatenation  of columns based on browser capabilities i.e. using HTML which will show the exactly how we do string concatenation.

Have a gr8 time…


Posted by on April 20, 2010 in OBI EE


6 responses to “Varchar2 greater than 4000 in OBIEE

  1. marcin

    April 29, 2010 at 8:49 am

    Hi Shiva,

    Are you sure it really works? I thought you cannot reference other columns. By putting @1 I see text of the current column concatenated with 1. As far as I know it only works in narrative view.


    • Shiva Molabanti

      May 2, 2010 at 6:08 pm

      Basically the narrative view is using the same html format coding.Based on this idea only I came to found this solution.
      And @1 means not concatenation of current column and 1. It means column at first position in your criteria.
      Did you try from your end? Definetly it works 🙂


      • Marcin

        May 4, 2010 at 8:41 am

        Hi Shiva,

        Perhaps I am missing something? What should be put in formula of 3rd column?
        I made an example based on Sample Sales. From the following answer I expect concatenated Product and LOB, whereas I get only Product concatenated with 1 and 2. I would be grateful for any hints on that.



      • Marcin

        May 4, 2010 at 9:28 am

        [saw:report xmlns:saw=”″ xmlns:xsd=”” xmlVersion=”200705140″ xmlns:xsi=”” xmlns:sawx=”″]
        [saw:criteria subjectArea=”"Sample Sales"”]
        [saw:column formula=”"D4 Product"."P01 Product"” columnID=”c2″/]
        [saw:column formula=”"D4 Product"."P03 LOB"” columnID=”c4″/]
        [saw:column formula=”"D4 Product"."P01 Product"” columnID=”c3″]
        [saw:displayFormat suppress=”default” interaction=”default” wrapText=”true”]
        [saw:dataFormat xsi:type=”saw:custom” customFormat=”@[html]@1@2″/][/saw:displayFormat]
        [saw:displayFormat interaction=”default”/]
        [saw:views currentView=”0″]
        [saw:view xsi:type=”saw:tableView” name=”tableView!1″ rptViewVers=”200510010″ showHeading=”false”/][/saw:views][/saw:report]

  2. Cédric (@cbourgeois)

    September 6, 2011 at 10:13 am

    I face the same issue with the column just showing 12 instead of the expected concatenation. I didn’t find any solution yet.
    Shiva, could you please add an XML of a working example of your solution?

    Thank you in advance.

  3. Cédric (@cbourgeois)

    September 6, 2011 at 1:33 pm

    My workaround is to generate a javascript code in the column formula, something like:

    ‘[script type=”text/javascript”]document.write(“‘ || “container1″.”field1″ || ‘” + “‘ || “container2″.”field2″ || ‘”));[/script]’

    and then to set the data format as HTML.
    This code assumes that the fields to merge won’t contain double quotes.
    My issue was not to display a long string but still to customize values with javascript, so some additional changes may be necessary for this to work…

    I hope this helps



Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: