I wanted to display an image in an Apex application in V3 but gave up because it was too hard (it was only for fun, didn’t need it).
Apex V4 now has a ‘display image’ item which makes it very easy.
First, you need to create a table in the database to hold the image. This in itself isn’t straight-forward, but this works:
Create the table:
create table pic(name varchar2(100),image blob);
Create the directory:
CREATE OR REPLACE DIRECTORY DOCUMENTS AS '/u01/app/oracle/dba/work/andy';
Make sure the picture file is in the directory you created:
pwd
/u01/app/oracle/dba/work/andy
ls rabbit.jpg
rabbit.jpg
Create the SQL to load the file into the database:
declare
l_blob blob;
l_bfile bfile;
begin
insert into pic values ( 'RABBIT', EMPTY_BLOB() )
returning image into l_blob;
l_bfile := bfilename( 'DOCUMENTS', 'rabbit.jpg' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
Note – the ‘RABBIT’ in the insert line is just a name, the ‘DOCUMENTS’ is the name of the directory you created, the ‘rabbit.jpg’ is the name of the picture file.
Run the SQL:
SQL> @load_pic
PL/SQL procedure successfully completed.
Check to see there is a column in the table:
SQL> select name from pic;
NAME
------
RABBIT
Commit, then exit from SQLPlus.
You then need to go into APEX and edit the application. Create a new page or a new region on a page, and create a new item. There is an item called "Display Image" so select that. You need to give it a name and then use the drop-down selection and choose "BLOB column returned by SQL Statement".
This is the SQL Statement to use:
select image from pic
Click "Next", then "Create Item" and "Run" the page. The image should be displayed.
