I started writing this post on an alternate method for loading custom images when using the Embedded PL/SQL Gateway (EPG) and then discovered that Mark Lancaster had recently blogged about the same thing on his blog. To recap, you can load your own images and files by replicating the method used when installing APEX with the EPG option which is contained in the apxldimg.sql script. Basically the script reads an xml file, imagelist.xml, to know which directories and files to upload. Hence, we need to create our own .xml file and rewrite the apxldimg.sql script for our purposes.
I'm going to try and show an example of how this may be done. The script files I have used can be downloaded here
I've modified the apxldimg.sql script to read an myimagelist.xml file and upload the included directories and files. I'll use Mark's imagelist.bat script to create the myimagelist.xml file.
I've a got a special file that I want to include in an application. I want it to be accessed by referencing /i/myimages/kidsmiley.gif
I create a directory structure on my hard drive as C:\apex\customimages\myimages and copy my kidsmiley.gif file into the myimages directory.
I place the imagelist.bat and myapxldimg.sql scripts in the customimages directory. I then use a command prompt and change my working directory to C:\apex\customimages and then execute the imagelist.bat script and redirect the results to the myimagelist.xml file.
C:\apex\customimages> imagelist.bat > myimagelist.xml
After the myimagelist.xml has been create I can now execute the myapxldimg.sql script by connecting to SQL*PLUS as SYS. Execute the myapxldimg.sql script and pass in the directory where the myimagelist.xml file is located.
My image has now been loaded and I can reference it from my application. I edit the page template of the sample application and place the image before the logo.
Then I run my application to see the result.
Keep the files and scripts in a safe place as you will need to rerun the script if you ever upgrade to a new version of Oracle Application Express (APEX).
Rem
Rem myapxldimg.sql
Rem
Rem NAME
Rem myapxldimg.sql - Application Express Load Images
Rem
Rem DESCRIPTION
Rem This script should be run as SYS and takes one
Rem argument, the path to the directory where the
Rem myimagelist.xml file and files to be uploaded exist.
Rem The script will load the images into XDB.
Rem
Rem MODIFIED (MM/DD/YYYY)
Rem jeberhard 08/20/2010
Rem Modified version of apxldimg.sql to load custom files.
Rem
timing start "Load Images"
begin
execute immediate 'drop directory APEX_IMAGES';
exception when others then
null;
end;
/
create directory APEX_IMAGES as '&1';
set serveroutput on
declare
file_list varchar2(30) default 'myimagelist.xml';
upload_directory_name varchar2(30) default 'APEX_IMAGES';
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);
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;
result := dbms_xdb.createfolder(directory_path);
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);
if instr(target_file_path,'/doc/ja/') = 1 and
target_file_name not like '%.xml' then
result := dbms_xdb.createresource(resource_path,
content_bfile,
nls_charset_id('AL32UTF8'));
else
result := dbms_xdb.createresource(resource_path,
content_bfile,
nls_charset_id('AL32UTF8'));
end if;
exception when others then
dbms_output.put_line('file not found: '||
target_file_path);
end;
end loop;
end;
/
commit;
timing stop
drop directory APEX_IMAGES;