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