9 Replies Latest reply: Jul 10, 2013 2:37 AM by DooRon RSS

    Help with displaying BLOBs in OBIEE 11g

    DooRon
      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