Displaying Images in Oracle Reports Using BFILE

Displaying Images in Oracle Reports Using BFILE

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • Blog
  • 3 mins read

Fetch and display images in Oracle Reports from the tables having BFILE column. BFILE is data type through which you can access image files stored on disk. To reference those files, there is a function BFILENAME in Oracle. Below is the example of displaying images in Oracle reports using BFILE column and BFILENAME function.

Create a Table in Oracle database With Column BFILE

CREATE TABLE product_master
(
product_id NUMBER,
product_name VARCHAR2 (100),
imgfile BFILE
);

And suppose we have following data in the above table, as shown in below screenshot.

displaying image in Oracle Reports

IMGFILE column is empty right now, we will update this column by giving reference to images on our system using BFILENAME function. To update image reference you should have product_Id value in your image file names for example img1000.jpg or 1000.jpg etc. So that you can easily update or insert the records into the table. Because to reference images at once, the image name should have some value matched to any column value in the table.

Update The IMGFILE column with Image reference using BFILENAME Function

UPDATE product_master
SET imgfile = BFILENAME ('IMGDIR', product_id || '.jpg');
Commit;

The above update statement will update product_master imgfile column with image reference. But you must have images with the name starting product_id e.g. 1000.jpg, 1001.jpg and 1002.jpg in IMGDIR for above example. IMGDIR is database directory object here. You must create an Oracle directory object for the path where the images are stored. Below is the example of creating Oracle database directory object.

Create Oracle Database Directory Object

Create or Replace Directory IMGDIR as 'F:\Temp\Imgfiles';

Images have been properly setup, now you can create an Oracle report to display data with images. Follow the below steps to create Oracle report.

Create Oracle Report

1. Start Oracle reports builder and create a tabular report by specifying the query as shown below:

SELECT product_id, product_name, imgfile 
FROM product_master

2.  After completing the wizard you will get the preview of the report, but it will not show the images. To display images open the property inspector for imgfile column and set the following property:

File Format: Image

You can also set the vertical elasticity and horizontal elasticity property to as per your report requirement.

Now when you will preview the report it will show the images. This method will work with all versions of Oracle Reprots 6i, 10g, and 11g.

Display images in Oracle Reports