APEX — Bulk Images Upload Using EPG

I was recently installing one APEX application and needed to upload a bunch of images. APEX was configured to use EGP (Embedded PL/SQL Gateway) so traditional options were to configure FTP or WebDAV but I’d rather not open these services on production environment.

After searching for the solution on the Internet, I surprisingly realized that there is none. At least, nothing I could find easily. Our resident APEX expert, Alex Fatkulin, pointed me to the installation process and suggested that there is a simple way to do that using a single PL/SQL call.

It turned out that it was more than a single PL/SQL call involved but nothing too difficult.

What you need is to create the hierarchy of files and directories that you want to upload (images or not – doesn’t matter). Then you create an XML file imagelist.xml listing required directories and files to upload.

Here is the example:

imagelist.xml
image1.png
logo/pythian.png

The content of imagelist.xml:

This folder should be on the database server and should be readable by the database server processes. The script accept the path to that directory as a parameter and creates a temporary directory object in the database. It then reads the XML file, creates directories inside XML DB repository and upload image objects. Finally, temporary directory database object is dropped.

The script is below. If a directory in XML DB repository already exists, then the script skips its creation (there is an exception handler). If uploaded file already exists then exception is thrown. Feel free to implement other behavior if you want to.

begin
execute immediate ‘drop directory APEX_IMAGES_UPLOAD’;
exception when others then
null;
end;
/

create directory APEX_IMAGES_UPLOAD as ‘&1’;

set serveroutput on

declare
file_list varchar2(30) default ‘imagelist.xml’;
upload_directory_name varchar2(30) default ‘APEX_IMAGES_UPLOAD’;
repository_folder_path varchar2(30);
pathseperator varchar2(1) := ‘/’;

directory_path varchar2(256);

target_folder_path varchar2(256);
target_file_path varchar2(256);
target_file_name varchar2(256);

resource_path varchar2(256);

filelist_xml xmltype := xmltype(bfilename(upload_directory_name,file_list),nls_charset_id(‘AL32UTF8′));
content_bfile bfile;

result boolean;

filelist_dom dbms_xmldom.domdocument;
files_nl dbms_xmldom.domnodelist;
directory_nl dbms_xmldom.domnodelist;
filename_nl dbms_xmldom.domnodelist;
files_node dbms_xmldom.domnode;
directory_node dbms_xmldom.domnode;
file_node dbms_xmldom.domnode;
text_node dbms_xmldom.domnode;
l_mv_folder varchar2(30);

DIR_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT(DIR_EXISTS, -31003);

begin

if wwv_flow_utilities.db_version_is_at_least(’11’) then
repository_folder_path := ‘/images/’;
else
repository_folder_path := ‘/i/’;
end if;

— create the set of folders in the xdb repository

filelist_dom := dbms_xmldom.newdomdocument(filelist_xml);

directory_nl := dbms_xmldom.getelementsbytagname(filelist_dom,’directory’);

for i in 0 .. (dbms_xmldom.getlength(directory_nl) – 1) loop
directory_node := dbms_xmldom.item(directory_nl,i);
text_node := dbms_xmldom.getfirstchild(directory_node);
directory_path := dbms_xmldom.getnodevalue(text_node);
directory_path := repository_folder_path || directory_path;
begin
result := dbms_xdb.createfolder(directory_path);
exception
when DIR_EXISTS then
null;
when OTHERS then
raise;
end;
end loop;

— load the resources into the xml db repository
files_nl := dbms_xmldom.getelementsbytagname(filelist_dom,’files’);
files_node := dbms_xmldom.item(files_nl,0);

filename_nl := dbms_xmldom.getelementsbytagname(filelist_dom,’file’);

for i in 0 .. (dbms_xmldom.getlength(filename_nl) – 1) loop
file_node := dbms_xmldom.item(filename_nl,i);

text_node := dbms_xmldom.getfirstchild(file_node);

target_file_path := dbms_xmldom.getnodevalue(text_node);
target_file_name := substr(target_file_path,instr(target_file_path,pathseperator,-1)+1);
target_folder_path := substr(target_file_path,1,instr(target_file_path,pathseperator,-1));
target_folder_path := substr(target_folder_path,instr(target_folder_path,pathseperator));
target_folder_path := substr(target_folder_path,1,length(target_folder_path)-1);
resource_path := repository_folder_path || target_folder_path || ‘/’ || target_file_name;

begin
content_bfile := bfilename(upload_directory_name,target_file_path);
result := dbms_xdb.createresource(resource_path,content_bfile,nls_charset_id(‘AL32UTF8’));
exception when others then
dbms_output.put_line(‘file not found: ‘||target_file_path);
end;

end loop;

end;
/

commit;

drop directory APEX_IMAGES_UPLOAD;

timing stop
[/sql]

There are some special conditions for 11g database but I warn you – I only tested it on 10g so far. Let me know how it works for you.