This discussion is archived
9 Replies Latest reply: Jul 10, 2013 12:37 AM by DooRon RSS

Help with displaying BLOBs in OBIEE 11g

DooRon Newbie
Currently Being Moderated
I am trying to get OBIEE 11g to display photographs in an Analysis report. I know BLOB fields are not supported, and I have been reading posts on this board and following examples on internet sites that try to get round this problem. But, try as I might, I cannot get those pesky photos to display.

Below are all the steps I have followed. Sorry that there is a lot to read, but I was hoping that somebody has been successful in doing this, and may spot something in one of my steps that I am doing wrong.

ORACLE TRANSACTIONAL SOURCE_

Table : EMPL_PHOTO
Fields:
USN VARCHAR2(11) ( Unique Key )
EMPLOYEE_PHOTO BLOB ( I think the photos are stored as 'png' )


ORACLE WAREHOUSE SOURCE_

Table : D_PERSON_PHOTO_LKUP
Fields :
PERSON_KEY     NUMBER(38,0) ( Primary Key - Surrogate )
USN     VARCHAR2(11)
PHOTO     CLOB


BLOB to CLOB conversion.
I used this function :

     create or replace function blob_to_clob_base64(p_data in blob)
     return clob
     is
     l_bufsize integer := 16386;
     l_buffer raw(16386);
     l_offset integer default 1;
     l_result clob;
     begin
     dbms_lob.createtemporary(l_result, false, dbms_lob.call);

     loop
     begin
     dbms_lob.read(p_data, l_bufsize, l_offset, l_buffer);
     exception
     when no_data_found then
     exit;
     end;
     l_offset := l_offset + l_bufsize;
     dbms_lob.append(l_result, to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_buffer))));
     end loop;

     return l_result;
     end;


     select usn, employee_photo ,
     BLOB_TO_CLOB_BASE64(employee_photo)
     from empl_photo

IN OBIEE ADMINISTRATION TOOL_

*1) Physical Layer*

Added D_PERSON_PHOTO_LKUP from Connection Pool
Left it as 'Cachable'
Didn't join it to any tables
Changed field PHOTO to a 'LONGVARCHAR' length 100000
Set USN as the Key ( not the surrogate key )

*2) BMM Layer*

Dragged D_PERSON_PHOTO_LKUP across.
Renamed it to 'LkUp - Photo'
Ticked the 'lookup table' box
Removed the surrogate key
Kept USN as the Primary key
The icon shows it similar to a Fact table, with a yellow key and green arrow.

On Dimension table D_PERSON_DETAILS (Dim - P01 - Person Details) added a new logical column
Called it 'Photo'
Changed the column source to be derived from an expression.
Set the expression to be :
Lookup(DENSE
"People"."LkUp - Photo"."PHOTO",
"People"."Dim - P01 - Person Details"."USN" )
Icon now shows an 'fx' against it.
Note: This table also had it Surrogate key removed, and USN setting as primary key.

*3) Presentation Layer*

Dragged the new Photo field across.

Saved Repository file, uploaded, and restarted server.

ONLINE OBIEE_

Created a new Analysis.
Selected USN from 'Person Details'
Selected Photo from 'Person Details'
Selected a measure from the Fact table

Under column properties of Photo ( data format ) :
- Ticked 'Override Default Data Format' box
- Set to Image URL
- Custom text format changed to : @[html]"<img alt="" src=""@H"">"

Under column properties of Photo ( edit formula ) :
- Changed to : 'data:image/png;base64,'||"Person Details"."Photo"

The Advanced tab shows the sql as :
     SELECT
     0 s_0,
     "People"."Person Details"."USN" s_1,
     'data:image/png;base64,'||"People"."Person Details"."Photo" s_2,
     "People"."MEASURE"."Count" s_3
     FROM "People"
     ORDER BY 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST
     FETCH FIRST 65001 ROWS ONLY
     
Going into the 'results' tab, get error message:

+State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 932, message: ORA-00932: inconsistent datatypes: expected - got CLOB at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)+

It doesn't seem to be using the Lookup table, but can't work out at which step I have gone wrong.

Any help would be appreciated.
Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points