BLOB & CLOB datatypes in Universe

building semantic layer
1

Hi,

Can we use BLOB & CLOB datatypes in BO universe?

Regards,
Vasu Ch

vasu.chinthoti (BOB member since 2012-05-31)

2

No, we can’t.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Thanks,

But what is the alternative solution to use those datatypes?

vasu.chinthoti (BOB member since 2012-05-31)

4

You can try for instance using the dbms_lob.substr() function to convert a CLOB to a VARCHAR, providing you use an Oracle database.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

[quote:5fca04f323=“vasu.chinthoti”]Thanks,

But what is the alternative solution to use those datatypes?
[/quote]

I don’t know if this still applies to XIR3 or even XIR4, but there was a workaround posted here a few years ago.[/url]

JPetlev (BOB member since 2006-11-01)

6

As i’m using Teradata database, i can use CAST() to convert CLOB datatypes to VARCHAR. But I heard that it is very expensive to maintain if data in CLOB type is morethan 2000 characters.

vasu.chinthoti (BOB member since 2012-05-31)

7

I came across Blob and Clob few years ago.
There were images stored in Blob format and I had to show them in webi, but universe doesn’t read Blob.
Hence I used Crystal Reports as it reads and displays images stored in Blob.

After few months, I again came across Blob, and this time there were images, Pdfs, Excel files and lot of other documents stored in Blob format.
Crystal could not read PDF, Word, Excel files stored in Blob.
Hence we copied all the files to sharepoint and added their respective hyperlinks in database in a column, and we used this column in Webi to open the file.

There were lot of projects in the pipeline wherein we had to do this, but this process was cumbersome. We had to upload all files to sharepoint, setup security there and lot of other stuff.

So in the next project, we built an application which reads files stored in Blob and allows reader to open or save it. We make a call to this application from Webi report through hyperlink. We pass the filename, database credentials and all other necessary info through the hyperlink which calls the application which in turn opens the file.

Building this application was one time effort. We used this application in lot of upcoming projects and saved a lot of time. Application was built, we just had to create hyperlinks in Webi and it saved huge amount of time.

Thanks,
Raghvendra

Raghvendra Deshpande :india: (BOB member since 2008-05-26)

8

I needed to know whether someone had an ID photo or not in our system (Oracle). It was stored as a blob. As previously mentioned, the below was used to create a string in a universe. In a webi report I check to see if the string is not null.

CAST(DBMS_LOB.SUBSTR(VIS.ID.PHOTO_BLOB, 100,1) as VARCHAR2 (300))

heronseye :us: (BOB member since 2008-11-12)

9

rizwan syed :us: (BOB member since 2010-07-08)

10

CLOB (Character large object) amass string data in the database quality set layout. Utilized for big strings or manuscript that utilizes the database character set-up completely. Characters in the database character set-up are in a permanent width layout.

http://www.inoracle.com/how-to-use-clob-datatype-in-oracle-10g/

swti6690 (BOB member since 2015-08-10)

11

Am i missing something @Marek Chladny ? Isn’t there a LONG “object type” in the universe with the specific purpose to handle CLOB data types? Works fine for displaying CLOB Data. Anything else like string manipulation etc - may need additional DB specific functions such as dbms_lob.substr, or just handle it in the report.

Shobhit_Acharya (BOB member since 2005-08-11)

12

Shobhit, did you find your answer ?

gadsden_consulting :us: (BOB member since 2015-06-18)