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.
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.