set define '^' verify off
prompt ...epg_install_images_nocdb.sql
--------------------------------------------------------------------------------
--
--  Copyright (c) Oracle Corporation 1999 - 2017. All Rights Reserved.
--
--    NAME
--      epg_install_images_nocdb.sql
--
--    DESCRIPTION
--      Install image files for EPG
--
--    MODIFIED   (MM/DD/YYYY)
--    hfarrell    12/20/2017 - Created
--
--------------------------------------------------------------------------------

begin
    execute immediate 'drop directory APEX_IMAGES';
exception when others then
    null;
end;
/

create directory APEX_IMAGES as '^1/images';

set serveroutput on

declare
    upload_directory_name   varchar2(30) default 'APEX_IMAGES';
    repository_folder_path  varchar2(30);
    directory_path          varchar2(256);
    resource_path           varchar2(256);
    content_bfile           bfile;
    directory_exists        boolean default false;
    result                  boolean;
    l_files                 dbmsoutput_linesarray;
    ro_anonymous_acl        varchar2(80) := '/sys/acls/ro_anonymous_acl.xml';

begin
  if wwv_flow_utilities.db_version_is_at_least('11') then
    repository_folder_path := '/images/';
  else
    repository_folder_path := '/i/';
  end if;

  l_files := dbmsoutput_linesarray (
               'flashchart/anychart_6/swf/OracleAnyChart.swf',
               'flashchart/anychart_6/swf/Preloader.swf',
               'flashchart/anychart_6/swf/fonts/Arial.swf',
               'flashchart/anychart_6/swf/fonts/Arial_Bold.swf',
               'flashchart/anychart_6/swf/fonts/Arial_Bold_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Arial_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Courier-New.swf',
               'flashchart/anychart_6/swf/fonts/Courier-New_Bold.swf',
               'flashchart/anychart_6/swf/fonts/Courier-New_Bold_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Courier-New_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Lucida-Sans.swf',
               'flashchart/anychart_6/swf/fonts/Lucida-Sans_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Trebuchet-MS.swf',
               'flashchart/anychart_6/swf/fonts/Trebuchet-MS_Bold.swf',
               'flashchart/anychart_6/swf/fonts/Trebuchet-MS_Bold_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Trebuchet-MS_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Verdana.swf',
               'flashchart/anychart_6/swf/fonts/Verdana_Bold.swf',
               'flashchart/anychart_6/swf/fonts/Verdana_Bold_Italic.swf',
               'flashchart/anychart_6/swf/fonts/Verdana_Italic.swf',
               'flashchart/anygantt_4/swf/AnyGantt.swf',
               'flashchart/anygantt_4/swf/Preloader.swf' );

  --
  -- For each directory, create it
  --
  dbms_output.put_line('...Create Flash directory structure');
  directory_path := repository_folder_path || 'flashchart/anychart_6/swf/fonts';
  begin
      for c1 in (select any_path
                   from xdb.resource_view
                  where any_path like directory_path )
      loop
          if xdb.dbms_xdb.existsResource(c1.any_path) then
              directory_exists := true;
          end if;
      end loop;  
      
      if not directory_exists then
          result := xdb.dbms_xdb.createfolder(directory_path);
      end if;
  end;

  --
  -- For each file, first remove it, then recreate it and apply the existing RO ACL
  --
  for i in 1..l_files.count loop

    resource_path := repository_folder_path || l_files(i);

    begin
        dbms_output.put_line('...Create file:' || resource_path);
        content_bfile := bfilename(upload_directory_name,l_files(i));
        if xdb.dbms_xdb.existsresource(resource_path) then
            xdb.dbms_xdb.deleteresource(resource_path,1);
        end if;
        
        if sys.dbms_lob.getlength(content_bfile) > 0 then
            result := dbms_xdb.createresource(resource_path,content_bfile,nls_charset_id('AL32UTF8'));
        end if;
    end;

    begin
        if wwv_flow_utilities.db_version_is_at_least('11') then
            dbms_xdb.setACL( resource_path, ro_anonymous_acl );
        end if;
    exception when others then
        dbms_output.put_line('.....Error applying ACL to file: ' || l_files(i));
    end;
  end loop;

end;
/

commit;

drop directory APEX_IMAGES;
