DanBrother的部落格

Step 2: Modify Data Access Descriptor (DAD) Configuration

Specify PlsqlDocumentTablename as "DANBROTHER.UPLOAD_FILES"
in DAD configuration file under
/u01/Middleware/asinst_1/config/OHS/ohs1/mod_plsql/dads.conf
>>
<Location /test>
SetHandler pls_handler
Order deny,allow
Allow from all
PlsqlAuthenticationMode          Basic
PlsqlDatabaseUsername          uploader
PlsqlDatabasePassword           OracleTest
PlsqlDocumentTablename        DANBROTHER.UPLOAD_FILES
PlsqlNLSLanguage                   AMERICAN_AMERICA.AL32UTF8
PlsqlSessionStateManagement StatelessWithFastResetPackageState
</Location>

Restart the Oracle HTTP Server as follows:
/u01/Middleware/asinst_1/bin/opmnctl restartproc process-type=OHS

Step 3: Write a PL/SQL Package for File Upload

The package will be created under DANBROTHER schema
and granted to uploader user for package execution.

create or replace package file_manage
is
    procedure upload (p_filename varchar2 default null);
    procedure view_content (p_filename varchar2 default null);
end file_manage;

create or replace package body file_manage
is
  procedure upload (p_filename varchar2 default null)
  is
  begin
     htp.formOpen('file_manage.upload',
                   cattributes => 'enctype="multipart/form-data"');
     htp.formfile('p_filename');
     htp.formSubmit(null,'upload');
     htp.formClose;

     if p_filename is not null then
        htp.p('<font color="red">'||p_filename||'</font> was uploaded
               successfully!<br>');
        htp.formOpen('file_manage.view_content');
        htp.formHidden('p_filename',p_filename);
        htp.formSubmit(null,'Display File Content');
        htp.formClose;
     end if;
     
        exception
            when others then
                 htp.p(dbms_utility.format_error_backtrace||' /
                       '||sqlerrm);
  end upload;

  procedure view_content
  (
     p_filename varchar2 default null
  )
  is
  TYPE color_table_type is table of varchar2(20)
  index by pls_integer;
  color_tab color_table_type;
  l_blob blob:=empty_blob();
  l_clob clob:=empty_clob();
  l_tmp_blob blob:=empty_blob();
  l_dest number:=1;
  l_scr number:=1;
  l_lang number default dbms_lob.default_lang_ctx;
  l_warning number:=1;
  color_ct pls_integer:=0;
  start_p pls_integer:=0;
  end_p pls_integer:=0;
  begin

  -- load blob to pl/sql table ------------------------------
  dbms_lob.createtemporary(l_clob,cache => false);
  dbms_lob.createtemporary(l_tmp_blob,cache => false);

  select blob_content into l_blob
  from upload_files
  where name = p_filename;

  dbms_lob.copy(l_tmp_blob,l_blob,dbms_lob.getlength(l_blob));
  dbms_lob.convertToClob(dest_lob => l_clob,
                         src_blob => l_tmp_blob,
                         amount => dbms_lob.getlength(l_tmp_blob),
                         dest_offset => l_dest,
                         src_offset => l_scr,
                         blob_csid => dbms_lob.default_csid,
                         lang_context => l_lang,
                         warning => l_warning);

  dbms_lob.freetemporary(l_tmp_blob);
  l_clob:=replace(l_clob,chr(13),';'); -- replace Carriage Return
  -- with semicolon(;)
  l_clob:=replace(l_clob,chr(10)); -- remove Line Feed

  while end_p<= dbms_lob.getlength(l_clob) loop
        color_ct:=color_ct+1;
        start_p:=end_p+1;
        end_p:=dbms_lob.instr(l_clob,';',start_p,1);
      if end_p<=0 then
         end_p:=dbms_lob.getlength(l_clob)+1;
      end if;
      color_tab(color_ct):=dbms_lob.substr(l_clob,end_p-start_p,start_p);
  end loop;

  ---------------------------------------------------------------------
  -- Display file content
  ---------------------------------------------------------------------
  if color_tab.COUNT > 0 then
     for i in color_tab.FIRST .. color_tab.LAST loop
         htp.p('<font color="'||color_tab(i)||'">'||color_tab(i)||'<br>');
     end loop;
  end if;

  htp.formOpen('file_manage.upload');
  htp.formSubmit(null,'Back to Home');
  htp.formClose;

      exception
          when others then
               htp.p(dbms_utility.format_error_backtrace||' / '||sqlerrm);
  end view_content;
end file_manage;

-- Grant package execution privilege to uploader
create public synonym file_manage for file_manage;
grant execute on file_manage to uploader;

Step 4: Test file upload

https:// 127.0.0.1:8080/test/file_manage.upload

SELECT * from UPLOAD_FILES;