Business Intelligence – Oracle

Oracle BI EE 10.1.3.32 – 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.

      ALTER USER ANONYMOUS ACCOUNT UNLOCK

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.

 CREATE OR REPLACE PROCEDURE print_employees IS
CURSOR emp_cursor IS SELECT last_name, first_name FROM hr.employees ORDER BY last_name;
BEGIN
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>List of Employees</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>List of Employees</h1>’);
HTP.PRINT(‘<table width=”40%” border=”1″>’);
HTP.PRINT(‘<tr>’);
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

      http://localhost:7779/apex/blob1.print_employees1

      

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
begin
if upper(procedure_name) in (‘BLOB1.PRINT_EMPLOYEES1′) then
return TRUE;
else
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

   ID   FILE_NAME   IMAGE_BLOB

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;
BEGIN
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
owa_util.http_header_close;
— 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

   http://localhost:7779/apex/blob1.custom_image_display?p_imageid=1

      

      

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.

      ‘<IMG SRC=”http://10.177.62.122:7779/apex/blob1.custom_image_display?p_imageid=’||CAST(RCOUNT(GEOGRAPHY.REGION_NAME) AS CHAR)||'” WIDTH=”41″ HEIGHT=”41″ BORDER=”0″ ALT=”BLOB IMAGES”>’

      

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

      

About these ads

5 Responses to “Oracle BI EE 10.1.3.32 – Displaying BLOB images – Using Pl/SQL Gateway”

  1. […] That will automatically configure the pl/sql gateway. For more details on this check my blog entry here. Once the above procedure has been compiled, lets grant execute on this procedure to anonymous so […]

  2. Narit said

    I am able to insert images into my reports in Oracle Answer and display it in the dashboard. My question is how do I download the entire report with the images.
    When downloading to PowerPoint or Excel, the images does not download. Only the location of the images are displayed or a red X.

  3. atlh said

    I want to store and display image in oracle database

  4. Chris said

    Hi,

    I am using the image BIC2GO and from what I can tell this is using the Oracle HTTP server and not the PL/SQL gateway as mentioned above.

    Has anyone had any experience setting up the display of BLOB images on this Image?

    Regards,
    Chris

  5. […] which is a erratic ORA error. Now try to google around what is the cause and landed to the Famous Blog of Venkat and yeah ! most of the information has been compiled nicely here and got some understanding as I […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: