Displaying an Image in APEX V4

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.

Script to create one user like another

This is from one of my colleagues.

We are often asked to create a new user based on an existing user, and we usually use Quest TOAD which has this feature. However, we look after a number of sites that don't have TOAD, and my colleague found this script to generate the SQL to create a new user:

set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "
spool newuser.sql
-- Create user...
select 'create user &&newname identified by &&psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to
&&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Set Default Role...
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';