APEX — Bulk Images Upload Using EPG

Jun 15, 2010 / By Alex Gorbachev

Tags: , ,

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.

2 Responses to “APEX — Bulk Images Upload Using EPG”

  • Andrew says:

    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;

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>