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