Business Intelligence – Oracle

Archive for December 10th, 2007

Oracle BI EE – Displaying BLOB images – Using Pl/SQL Gateway

Posted by Venkatakrishnan J on December 10, 2007

I had received a couple of emails last weekend on how to go about displaying images stored inside an Oracle Database as a BLOB field in OBI EE. Well, i have had this question asked before by some customers with whom i had interacted in the past. So, i thought of blogging about it so that others can use the same. Before proceeding further, just a word of notice, BLOB images are not directly supported in OBI EE. i.e. One cannot import these fields as normal fields in the repository and then make them display in BI EE. So, in order to display the BLOB images in OBI EE, we would be using a feature of Oracle Database called as “Pl/SQL gateway”. So, what is a pl/sql gateway? To keep it simple, PlSQL gateway is a feature wherein one can expose Oracle Pl/SQL packages to a web browser. It is similar to a HTTP server but would be using Pl/SQL packages. A very good example of a sample application that uses plsql gateway is the Application Express 3.0. If you had installed 11g, you would have noticed that APEX 3.0 gets installed by default and also the Pl/SQL gateway gets configured by default. One of the main reasons of using PlSQL gateway is to have a mechanism wherein we can display all BLOB images over a web browser. So, basically we would be using the PlSQL gateway a call a procedure which in turn would download a stored BLOB image. In the example to follow, i would be using the 11g plsql gateway and APEX 3.0. If we configure APEX 3.0, the gateway also gets configured. To configure APEX 3.0 in 11g, there are 2 simple steps

1.   Run the @apxconf.sql (under {ORACLE_HOME}/apex) as Sys. It would ask you for a port and password. In my case, i have used 7779 as the port.


2.   Unlock the Anonymous Account.


Now, you should be able to log into APEX using this URL http://localhost:7779/apex.


This would mean that pl/sql gateway is working. If you watch closely, the URL would have been modified to http://localhost:7779/apex/f?p=1232353. So, what this means is that APEX by default calls a procedure named “f” which accepts parameter p. So, lets test this out by compiling a sample procedure (which would print employee details. I have taken this directly from the docs to keep it simple) and by calling this procedure from the gateway. The procedure is given below and i am compiling this from a schema called blob1.

CURSOR emp_cursor IS SELECT last_name, first_name FROM hr.employees ORDER BY last_name;
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>List of Employees</title>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>List of Employees</h1>’);
HTP.PRINT(‘<table width=”40%” border=”1″>’);
HTP.PRINT(‘<th align=”left”>Last Name</th>’);
HTP.PRINT(‘<th align=”left”>First Name</th>’);
HTP.PRINT(‘</tr>’); FOR emp_record IN emp_cursor LOOP HTP.PRINT(‘<tr>’);
HTP.PRINT(‘<td>’ || emp_record.last_name || ‘</td>’);
HTP.PRINT(‘<td>’ || emp_record.first_name || ‘</td>’); END LOOP;
HTP.PRINT(‘</table>’); HTP.PRINT(‘</body>’); HTP.PRINT(‘</html>’); END;

      As you see what the above procedure does is, it prints out all the employee details. HTP is a system package to make browser calls. Now lets call this procedure from the URL using the same syntax as above. The URL would look like this



What happened? Why do we get a Error:404 page not found error. This means that the ANONYMOUS account(ANONYMOUS should have this privilege since we are not giving any login details) does not have a execute privilege on this package. So, lets give the privilege and try that out.



What happened now? We are geting a Error:403 Page forbidden error. This means that we are able to access the package but pl/sql gateway does not allow us to execute this. In order to make plsql gateway to allow this procedure to execute, we need to add our procedure to the list of allowed procedures in the wwv_flow_epg_include_mod_local function. Use the below function to add out procedure to the list

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
if upper(procedure_name) in (‘BLOB1.PRINT_EMPLOYEES1’) then
return TRUE;
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

Thanks to the APEX blogs here and here for giving the details on the above function. Now lets compile the above function as FLOWS_03000 and see what happens.



So, this time you would be seeing the list of employees in a web browser. Now that we are sure about the list of steps to configure PlSQL gateway to run desired procedures lets look at how to go about configuring the same for downloading BLOB images. The first step is to create a table containing an id field and corresponding image BLOB field. If you are not sure about the insert syntax for BLOB’s i would recommend using a shareware utility called as OracleLOB Editor. In my case, i have created a table having 3 fields


And i have inserted images into my IMAGE_BLOB field. The next step is to write a procedure which can download the images. I will be using the procedure below (taken from the docs here

CREATE OR REPLACE procedure custom_image_display(p_imageid in number) as
v_mime varchar2(255);
v_length number;
v_file_name varchar2(2000);
Lob_loc BLOB;
select ‘image/gif’, image,file_name,dbms_lob.getlength(image)
into v_mime,lob_loc,v_file_name,v_length
from hr.blob_image
where id = p_imageid;

— set up HTTP header

— use an NVL around the mime type and
— if it is a null set it to application/octect
— application/octect may launch a download window from windows
owa_util.mime_header( nvl(v_mime,’application/octet’), FALSE );

— set the size so the browser knows how much to download
htp.p(‘Content-length: ‘ || v_length);
— the filename will be used by the browser if the users does a save as
htp.p(‘Content-Disposition: attachment; filename=”‘ || v_file_name || ‘”‘);
— close the headers
— download the BLOB
wpg_docload.download_file( Lob_loc );
end custom_image_display;

Now compile this procedure and grant execute privilege to the anonymous user. Also, add this procedure to the wwv_flow_epg_include_mod_local function as mentioned above. Now test the procedure using the below URL




In your case, it might display the image or would show a pop up to download the image. If you get either of them then the Procedure and the PlSQL gateway are working fine. Now go to BI EE Answers and we would be using the same technique to display the images as mentioned in my previous blog entry here.

Lets start with creating a sample report. We shall include Region, ImageID (a running number to pass the id to the procedure above) and the image column.


In the image column, enter the following formula and change the data format to HTML.



Now, if you view the report, you shall see the blob images being rendered directly within BI EE.


Posted in All Posts, APEX, OBI EE Plus | 5 Comments »