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:

<upload>
    <directories>
        <directory>logo</directory>
    </directories>
    <files>
        <file>/image1.png</file>
        <file>/logo/pythian.png</file>
    </files>
</upload>


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.

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

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 *