APEX — Bulk Images Upload Using EPG

Posted in: Technical Track

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:

[xml] <upload>
<directories>
<directory>logo</directory>
</directories>
<files>
<file>/image1.png</file>
<file>/logo/pythian.png</file>
</files>
</upload>
[/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.

[sql] timing start "Load Images"

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.

email

Interested in working with Alex? Schedule a tech call.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

2 Comments. Leave new

This apxldimg.sql based solution all looks awfully complicated for someone just wanting to upload a few images. A simplified example is:
DECLARE
rv BOOLEAN;
BEGIN
BEGIN
–public
DBMS_XDB.deleteresource (‘/public/my_image.jpg’, DBMS_XDB.DELETE_FORCE);
EXCEPTION
WHEN OTHERS THEN NULL;
END;

— MY_DIR is on the Oracle server!
rv := DBMS_XDB.createResource (‘/public/my_image.jpg’,
BFILENAME (‘MY_DIR’, ‘/public/my_image.jpg’),
NLS_CHARSET_ID (‘AL32UTF8’));
IF NOT rv THEN
raise_application_error (-20501, ‘Failed to load result image into XDB’);
END IF;
END;

Reply
Alex Gorbachev
July 30, 2010 12:47 pm

I guess that should work too. Thanks Andrew.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *