DanBrother的部落格

@echo off
@REM *****************************************************************
@REM * File:    myfilelist.bat
@REM * Author:  Mark Lancaster May 2012
@REM * Purpose: For APEX applications using the Embedded PL/SQL Gateway.
@REM             Generate XML list of directories and files suitable
@REM             for loading into Oracle XDB.
@REM             
@REM             Refer APEX file "apxldimg.sql" for example usage.
@REM
@REM             Direct output to a file e.g.  myfilelist.xml
@REM * Revs:     2012-AUG-20:jeberhard    add logic to exclude a couple of
@REM                                    files from the list of files
@REM *****************************************************************

setlocal enabledelayedexpansion

echo ^<upload^>
echo     ^<directories^>

REM list directories with relative path

for /F "tokens=*" %%c in ('dir /ad /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  echo         ^<directory^>!relpath!^<^/directory^>
)

echo     ^<^/directories^>
echo     ^<files^>

REM list files with relative path and leading slash

for /F "tokens=*" %%c in ('dir /a-d /ogn /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  if not '!relpath!' == 'myfilelist.bat' if not '!relpath!' == 'myfilesld.sql' echo         ^<file^>^/!relpath!^<^/file^>
)

echo     ^<^/files^>
echo ^<^/upload^>

Rem
Rem myfilesld.sql
Rem
Rem    NAME
Rem      myfilesld.sql - Application Express Load Images
Rem
Rem    DESCRIPTION
Rem      This script should be run as SYS and takes one argument, the path
Rem      to the directory where the myfilelist.xml file and files to be uploaded exist.
Rem      The script will load the files into XDB.
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem     DanBrother  02/24/2016 - Modified version of apxldimg.sql to load custom files.
Rem

timing start "Load Files"

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

create directory MY_FILES as '&1';

set serveroutput on

declare
    file_list               varchar2(30) default 'myfilelist.xml';
    upload_directory_name   varchar2(30) default 'MY_FILES';
    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 := '/test/';
  else
    repository_folder_path := '/i/';
  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;
end;
/

commit;

timing stop

drop directory MY_FILES;

create procedure test_page
is
begin 
    htp.p('<a target="_blank" href="/test/myfiles/1.txt">download file1</a><br>
    <br>
    <a target="_blank" href="/test/myfiles/2.txt">download file2</a><br>
    <br>
    <a target="_blank" href="/test/myfiles/3.txt">download file3</a></p>');  
end test_page;

http://127.0.0.1:8080/demo/test_page