download file - liamlamth/blog GitHub Wiki

• dynamic actions: click button
• (optional) execute server-side code
  • PL/SQL: null;
  • Items to Submit: page items will be used for the file generation PL/SQL
• execute javascript code
  • javascript:window.open('f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=download_file', '_self');

• process point: ajax callback: run this application process when requested by a page process.
• name: download_file
• PL/SQL

  Declare
      v_zip blob;
      v_file blob;
  
      cursor c0 is
          --SQL                                                                                                               --...File Separation SQL
  
      cursor c1 (in_file_number number) is
          --SQL                                                                                                               --...Report Body SQL   
  
  BEGIN
      
      apex_collection.create_or_truncate_collection('ZIP');
  
  	for c0_rec in c0 loop                                                                                               --generate files loop
  		dbms_lob.createtemporary(v_file, TRUE);
  		
  		for c1_rec in c1(c0_rec.file_number) loop                                                                   --...adjust passing parameter as needed
  		
  			dbms_lob.append(v_logfile, utl_raw.cast_to_raw('file line 1' || c1_rec.report_line ||chr(13)));     --append line to the file
                                                                                                                              --...adjust used column as needed
  		end loop;
  		apex_zip.add_file (v_zip, 'file_'||c0_rec.file_number || '.txt', v_file);                                   --append file to the zip
  		                                                                                                            --...adjust file name as needed
  		dbms_lob.freetemporary(v_file);
  	end loop;
  	
      apex_zip.finish(v_zip);
      apex_collection.add_member(
          p_collection_name => 'ZIP',
          p_blob001            => v_zip);
                                                                                                                              --html return
      htp.flush;
      htp.init;
      owa_util.mime_header('application/zip', false);
      htp.p('Content-length: '||dbms_lob.getlength(v_zip));
      htp.p('Content-Disposition: attachment; filename="reports.zip"');                                                       --...adjust zip name as needed
      htp.p('Set-Cookie: filedownload=true; path=/');
  
      owa_util.http_header_close;
      wpg_docload.download_file(v_zip);
  END;
  

grant read on UTL_DIR to APEX_PUBLIC_USER;
grant read on UTL_DIR to WORKSPACE_SCHEMA;

• process point: ajax callback: run this application process when requested by a page process.
• name: download_utl_file
• PL/SQL

  DECLARE
      v_file_name  varchar2(200);
      v_length     number;
      lob_loc      bfile;
      v_errcode    number;
  BEGIN
      v_file_name := :P10_FILE_NAME;
      lob_loc := bfilename('UTL_DIR', v_file_name);
  
      owa_util.mime_header('text/plain', False);
      htp.p('Content-length: ' || dbms_lob.getlength(lob_loc));
      htp.p('Content-Disposition: attachment; filename="' || SUBSTR(v_file_name, INSTR(v_file_name, '/') + 1) || '"');
      owa_util.http_header_close;
      wpg_docload.download_file(lob_loc);
  END;
  

• authentication: Page Is Public
• page item: P10_FILE_NAME, P10_FILE_SIZE, P10_FILE_STATUS, FILE_ID, P10_ERR_MSG
  *optional: hide item by server condition
• button: DOWNLOAD
  dynamic actions:
  • Execute Server-side Code >> PL/SQL (i) null; (ii) Items to Submit: P10_FILE_NAME
  • Execute JavaScript >>javascript:window.open('f?p=&APP_ID.:10:&SESSION.:APPLICATION_PROCESS=download_utl_file:NO', '_self');
• prcosses after header: execute code - PL/SQL

  DECLARE
      v_file_exists   BOOLEAN;
      v_file_length   NUMBER;
      v_block_size    BINARY_INTEGER;
      v_file_length_h VARCHAR2(100);
      v_errcode       NUMBER;
  
  begin
      select null into :P10_ERR_MSG from dual;
      select utl_raw.cast_to_varchar2(dbms_crypto.decrypt(:FILE_ID, 4353, utl_raw.cast_to_raw ('LIAM.LAMTH'))) into :P10_FILE_NAME from dual;
  
      UTL_FILE.FGETATTR(
          'UTL_DIR',
          :P10_FILE_NAME,
          v_file_exists,
          v_file_length,
          v_block_size
          );
  
      if v_file_exists then
          select 'Avaliable' into :P10_FILE_STATUS from dual;
          select case when v_file_length < power(1024,1) then v_file_length || ' Byte'
                      when v_file_length < power(1024,2) then round(v_file_length/power(1024,1), 0) || ' KB'
                      else round(v_file_length/power(1024,2), 0) || ' MB'
                  end into :P10_FILE_SIZE from dual;
      else
          select 'Expired' into :P10_FILE_STATUS from dual;
          select '-' into :P10_FILE_SIZE from dual;
      end if;
  
      exception
          when others then
              v_errcode := sqlcode;
              if v_errcode = -28817 then
                  select 'Invalid URL' into :P10_ERR_MSG from dual;
              end if;
      
  END;