Here is the problem: How do I create a BusinessObjects object based on a Oracle 8.1 CLOB column? If I set the BusinessObjects object type to either “Character” or “Long Text” I get the error message “expression type not comptiable with object type.” If I use the Oracle DBMS_LOB.SUBTR() method I can get the object to parse Ok if I select “Character” for the object type (I cannot choose Long Text, it gives me the “expression type…” error). The problems is then, on some records, I get an ORA-06502 error Character buffer too small.
I seems to me that this is a BusinessObjects issue since I can find no way to set the object type to “Long Text” and still have it parse ok.
Michael Welter
Sr. Technical Analyst
Verizon Wireless
Date Entered
08-OCT-1999
Product Release
BusinessObjects 5.0
Problem
How to use Oracle’s CLOB data type in BusinessObjects? Resolution CONFIGURATION*
BusinessObjects version 4.x and 5.x
Oracle WARNING**
The CLOB datatype is unique to Oracle and is not fully supported by Business Objects.
****RESOLUTION
CLOB datatype means single-byte character data.
Databases have long been used to store large objects. Oracle 8 introduced the Large Object(LOB)datatypes. LOBs can be broadly categorized as Internal LOBs and External LOBs.
Internal LOBS can store binary data (BLOBs), single-byte character data (CLOBs), or multi-byte character data (NCLOBs). Internal LOBs are stored in the database tablespaces in a way that optimizes space and provides efficient access.
External LOBs, are stored in operating system files outside the database tablespaces. These LOBs do not participate in transactions.
Selecting a LOB returns the LOB locator instead of the LOB value. Two new special functions in Oracle8 SQL DML, EMPTY_BLOB() and EMPTY_CLOB(), allow initialization of NULL or non-NULL LOB columns to empty.
To visualize the CLOB type:
Check whether a file called: DBMS_LOB.SQL is installed on your computer. If not install it from the Oracle 8 CD. It contains the SQL functions needed to query a CLOB.
Create a new object in designer.
Change the object type to Long text.
In the select clause, use the following syntax : dbms_lob.substr(name of the CLOB column, number of caracters to be returned, start). If you need to get the Clob length, use the function : dbms_lob.length(CLOB column name)
Uncheck the limitation on the long objects in File menu > Parameters command > Controls tab.
If you try to visualize the values in Designer, (Right click on a table in table structure > View table values) you will note that it is impossible. You must use of the function above to visualize a CLOB.
Note: If you create the object by dragging the column in the object window, the object type might be ‘BLOB’ instead of ‘Long Text’. You can correct this easily.
Check whether a file called: DBMS_LOB.SQL is installed on your computer. If not install it from the Oracle 8 CD. It contains the SQL functions needed to query a CLOB.
Create a new object in designer.
Change the object type to Long text.
In the select clause, use the following syntax : dbms_lob.substr(name of the CLOB column, number of caracters to be returned, start). If you need to get the Clob length, use the function : dbms_lob.length(CLOB column name)
Uncheck the limitation on the long objects in File menu > Parameters command > Controls tab.
Steve,
We tried this already. BO Tech support sent us this. However, we have been unable to find a file called DBMS_LOB.SQL. I searched my hard drive for it. I searched the Oracle CD for it. I searched the internet for it. It doesn’t seem to exist. Has anyone had any luck with this?
Michael Welter
Sr. Technical Analyst
Verizon Wireless
Dredging this up from the archives – we’ve tripped upon this too. So, Steve, Michael, others — where can one find this file on an Oracle CD, and then where does it go on one’s PC once one has it? And then does something have to be done to each and every user’s desktop who’s going to be using the object created? Or once the Designer does it that’s all?
We have used this successfully. I do not believe there is anything to install locally as these come from the DBMS_LOB package residing on the database server. A full description of the DBMS_LOB objects can be found in the “Oracle Complete Reference.”
To use them, define your object as long text and include a function like this in your select:
CLOB is going to be easier in version 6, although you have to manually create the object. If Oracle 8i your object def is like dmbs_lob.substr(name of column,#chars,position of 1st char) and in 9i just name of column. It specifically says that BLOBs aren’t supported.
It is mentioned we can use DBMS_LOB.LENGTH() for retrieving the length of the CLOB column.
I was unable to find that function, but i am able to retrieve the length of the CLOB column using the function DBMS_LOB.SUBSTR()
I created an object (Character - Datatype) in BO for CLOB column in oracle Select Clause:
DBMS_LOB.SUBSTR(CLOB_Column,DBMS_LOB.GETLENGTH(CLOB_Column),1)
And it works good. I am able to use this objects for conditions also.
I’m running BO 6.5 against Oracle 9i and was thrown my first CLOB by our ETL folks. I needed to pull the full text of the object in a report, some of the values for which exceed 4000 bytes, so the DBMS_LOB function was just the ticket.
One interesting thing that I wanted to add to the discussion is that given my environment, I have to pull the CLOB in 4000 byte increments and concat them back together-- and that this has to be done in the report. If I try to do it in the universe object definition, I get the error
But using the concat function in the full-client reporter avoids the problem.
Hi Rama Krishna,
I have the same requirements. I meant I need to display Image (which are stored in DB columns) on Webi reports. I created an object using Select Clause:
DBMS_LOB.SUBSTR(CLOB_Column,DBMS_LOB.GETLENGTH(CLOB_Column),1) but parsing is fail ('The expression type is not compatible with the object type) for both char type (Char and Long Text). I export the universe and try to run the query it gives me Invalid Database field type. Any idea what I need to add my select statement in designer so that I can get Image on my reports?
But the same query successfully ran in DESKI but just display a LINE only on IMAGE field.
I am using oracle 10g
I’m using BO 6.5: I use Oracle 9i connected through OCI and I have a view that carries a CLOB field.
To let BO reports deals correctly with fields larger than 4000 characters, just set the Designer’s large texts limit size to a very high value (ex. 25000).
This can be found in File–>Parameters—>Control Tab–> Limit size of long text objects to:" (enter a value)
As a matter of facts, the Online Help Guide says: “Note: When this check box is not selected, the parameter is not activated. It is automatically set to the default maximum value (1000). To ensure that you allow results larger than the default, the check box must be selected, and a value entered.”
(BOB member since 2002-06-07)
(BOB member since 2003-04-22)
(BOB member since 2002-08-15)
(BOB member since 2004-12-07)
(BOB member since 2003-05-08)
(BOB member since 2004-12-07)
(BOB member since 2008-09-12)