Rem Rem Rem apex_epg_config_core.sql Rem Rem Copyright (c) 2004,2006, Oracle Corporation. All rights reserved. Rem Rem NAME Rem apex_epg_config.sql - Application Express Embedded PL/SQL Gateway Configuration Rem Rem DESCRIPTION Rem This script should be run as SYS and takes one argument, the path Rem to the Oracle Home. The script will load the images into XDB and then configure Rem a DAD for use by Application Express in the Embedded PL/SQL Gateway. Rem Rem Rem MODIFIED (MM/DD/YYYY) Rem jstraub 09/22/2004 - Created Rem jstraub 10/07/2004 - Added character set ids to call to dbms_xdb.createresource Rem jduan 07/27/2005 - Enable anonymous access to XDB repository Rem jduan 08/08/2005 - Enable database version checking for tag Rem jduan 08/08/2005 - Modified the logic to insert anonymous access tag only when Rem it is not present in the config file. Otherwise, update its value to 'true.' Rem jduan 09/08/2005 - Move the section of adding two mime types to before the file loading section. It is Rem necessary to set the correct mime types before loading the file to XDB. Otherwise, files Rem with "xbl" extension would not be treated as an xml file. Rem jkallman 09/22/2005 - Configure with ANONYMOUS instead of HTMLDB_PUBLIC_USER Rem jkallman 09/30/2005 - Configure nls-language DAD attribute Rem jkallman 10/25/2005 - Remove unnecessary authorize_dad call as a result of transaction rpang_authorize_dad_opt Rem jkallman 11/29/2005 - Set attribute request-validation-function in DAD configuration Rem jkallman 12/03/2005 - Modify request-validation-function to reference package and not procedure Rem jduan 12/07/2005 - Add the index.htm link that points to the license agreement file Rem jduan 12/07/2005 - Remove several lines of code that add the anonymousServletRole Rem jkallman 12/13/2005 - Rename DAD and virtual path from HTMLDB to APEX (Bug 4879917) Rem jduan 12/13/2005 - Using insertxmlbefore to enable allow-repository-anonymous-access when http-host exist (Bug 4886392) Rem jkallman 12/13/2005 - Change DAD default-page from htmldb to apex (Bug 4879917) Rem jduan 12/15/2005 - Change logic for add allow-repository-anonymous-access element to use insertchildxml sql function Rem jkallman 02/26/2006 - Copied for original htmldb_epg_config.sql Rem jstraub 07/06/2006 - Removed spooling and setting XE start page to adapt for upgrade from XE to SE/EE Rem jstraub 07/12/2006 - Added call to deleteresource to remove link to index.htm Rem jstraub 07/24/2006 - Added logic to check for existance of directory /i/, if exists, move it to new folder Rem jstraub 07/24/2006 - Added check for existence of /index.html before removing link Rem jstraub 08/29/2006 - Added call to dbms_epg.allow_anonymous_access, ignore if doesn't exist, raise if other error (bug 5366888) Rem jstraub 12/19/2006 - Removed call to dbms_epg.allow_anonymous_access on advice from rpang Rem jstraub 01/19/2007 - Moved setting allow-repository-anonymous-access from apex_epg_config.sql Rem jstraub 02/21/2007 - Added code from rpang to configure new /i/ anonymous servlet Rem jstraub 03/21/2007 - Replaced UnauthenticatedFileAccessServlet with PublishedContentServlet per rpang Rem jstraub 05/30/2007 - Altered to be multi-use for XE upgrade to 10.2.0.3 and for 11g Rem jstraub 06/01/2007 - Added existance check for mime extenstion before adding to the xdbconfig.xml Rem jstraub 06/07/2007 - Added other existance checks for images servlet and ACL to make script re-runnable Rem jkallman 06/26/2007 - Always upload doc_map.xml encoded as AL32UTF8 Rem jstraub 03/03/2008 - Adapted for 11.2 upgrade/downgrade Rem jstraub 09/09/2008 - Move logic from apex_epg_config.sql to support multiple distribution methods Rem jkallman 01/09/2009 - Load all JA online help files with .xml extension as AL32UTF8 (Bug 7700562) Rem jkallman 01/09/2009 - Delete incorrectly encoded files in JA online help (Bug 7700562) Rem jkallman 07/01/2009 - Change JA16SJIS charset reference to AL32UTF8 to account for Japanese online help encoding Rem --add two mime types declare TYPE r_type IS RECORD ( ext VARCHAR2 (32), mime VARCHAR2 (256) ); TYPE tr_type IS TABLE OF r_type; l_rarray tr_type := tr_type(); l_rec r_type; cfg XMLType; begin l_rec.ext := 'htc'; l_rec.mime := 'text/x-component'; l_rarray.extend; l_rarray(1) := l_rec; l_rec.ext := 'xbl'; l_rec.mime := 'text/xml'; l_rarray.extend; l_rarray(2) := l_rec; cfg := dbms_xdb.cfg_get(); for i IN l_rarray.FIRST .. l_rarray.LAST loop if cfg.existsNode('/xdbconfig/sysconfig/protocolconfig/common/extension-mappings/mime-mappings/mime-mapping/extension[text()="'||l_rarray(i).ext||'"]') = 0 then select insertChildXML(cfg, '/xdbconfig/sysconfig/protocolconfig/common/extension-mappings/mime-mappings', 'mime-mapping', XMLType(''||l_rarray(i).ext||' '||l_rarray(i).mime||' ')) into cfg from dual; dbms_xdb.cfg_update(cfg); end if; end loop; if wwv_flow_utilities.db_version_is_at_least('11') then --11g only -- Add "PublishedContentServlet" and -- publish "/images" folder in XDB repository as "/i" in HTTP if cfg.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings/servlet-mapping/servlet-name[text()="PublishedContentServlet"]') = 0 then cfg := cfg.appendChildXML('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-mappings', XMLType(' /i/* PublishedContentServlet ')); end if; if cfg.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list/servlet/servlet-name[text()="PublishedContentServlet"]') = 0 then cfg := cfg.appendChildXML('/xdbconfig/sysconfig/protocolconfig/httpconfig/webappconfig/servletconfig/servlet-list', XMLType( ' PublishedContentServlet C Unauthenticated File Access Servlet Servlet for files for unauthenticated users RootFolder /images RootFolder anonymousServletRole anonymousServletRole ')); dbms_xdb.cfg_update(cfg); end if; end if; commit; dbms_xdb.cfg_refresh; end; / timing start "Load Images" begin execute immediate 'drop directory APEX_IMAGES'; exception when others then null; end; / create directory APEX_IMAGES as '&1/apex/images'; set serveroutput on declare file_list varchar2(30) default 'imagelist.xml'; upload_directory_name varchar2(30) default 'APEX_IMAGES'; repository_folder_path varchar2(30); pathseperator varchar2(1) := '/'; index_file varchar2(30) default 'index.htm'; license_file varchar2(30) default 'doc/ee_db_xe_agmt.htm'; 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; if dbms_xdb.existsresource(repository_folder_path) then for c1 in (select to_char(sysdate,'YYYYMMDDHH24MI') x from dual) loop l_mv_folder := substr(repository_folder_path,2,instr(repository_folder_path,'/',-1)-2)||'_'||c1.x; end loop; if '&IMGUPG' != '' then l_mv_folder := '&IMGUPG'; end if; dbms_xdb.renameresource(repository_folder_path,'/',l_mv_folder); end if; result := dbms_xdb.createfolder(repository_folder_path); -- 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; -- need to remove index.htm link to XE license page if dbms_xdb.existsresource('/'||index_file) then dbms_xdb.deleteresource('/'||index_file,1); end if; end; / commit; -- -- During an upgrade of Application Express, the previous images directory is copied and renamed. There are -- resources in the images directory shipped with Application Express 3.0 and DB 11.1.0.6 which were marked as -- encoded in Shift JIS when, in fact, they were encoded in utf-8. Locate these resources and delete them. -- begin for c1 in (select any_path from xdb.resource_view where any_path like '/images_200%' and (any_path like '%ja/toc.xml' or any_path like '%ja/TDPAX/toc.xml') and extractvalue(res,'/Resource/CharacterSet/text()') = 'SHIFT_JIS') loop -- dbms_xdb.deleteresource( abspath => c1.any_path, delete_option => dbms_xdb.delete_force ); end loop; -- commit; end; / Rem Creates a new ACL for "/images" and give ANONYMOUS read-only access to it, Rem ACL for the root folder and gives ANONYMOUS read access to it also declare ro_anonymous_acl varchar2(80) := '/sys/acls/ro_anonymous_acl.xml'; procedure publish_folder(folder varchar2, acl varchar2) is begin for r in (select r.any_path path from resource_view r where under_path(r.res, folder) = 1 or equals_path(r.res, folder) = 1) loop dbms_xdb.setACL(r.path, acl); end loop; end; begin if wwv_flow_utilities.db_version_is_at_least('11') then --11g only if not dbms_xdb.existsResource(ro_anonymous_acl) and (not dbms_xdb.createResource(ro_anonymous_acl, XMLType(' true ANONYMOUS '))) then raise program_error; end if; publish_folder('/images', ro_anonymous_acl); if (not dbms_xdb.changePrivileges('/', XMLType(' true ANONYMOUS ')) > 0) then raise program_error; end if; end if; end; / timing stop alter session set current_schema = XDB; begin dbms_epg.create_dad('APEX','/apex/*'); dbms_epg.set_dad_attribute('APEX','database-username','ANONYMOUS'); dbms_epg.set_dad_attribute('APEX','default-page','apex'); dbms_epg.set_dad_attribute('APEX','document-table-name','wwv_flow_file_objects$'); dbms_epg.set_dad_attribute('APEX','document-path','docs'); dbms_epg.set_dad_attribute('APEX','nls-language','american_america.al32utf8'); dbms_epg.set_dad_attribute('APEX','document-procedure','wwv_flow_file_mgr.process_download'); dbms_epg.set_dad_attribute('APEX','request-validation-function','wwv_flow_epg_include_modules.authorize'); end; / commit; alter session set current_schema = SYS; drop directory APEX_IMAGES;