DanBrother的部落格

create or replace procedure download_dictionary_to_excel
(p_download pls_integer default 0
)
   /******************************************************/
  /* Download Oracle Dictionary to Excel File - Example */ 
 /* Programmed by DanBrother                           */
/******************************************************/

is
type dictionary_record is table of dictionary%rowtype
index by pls_integer;
dictionary_tab dictionary_record;

v_filename varchar2(200);
begin
    if p_download = 1 then       
      select table_name,comments
      bulk collect into dictionary_tab
      from dictionary
      order by table_name;
            
      v_filename:='Oracle字典(全).xls';    
      v_filename:=utl_url.escape(v_filename,url_charset => 'UTF-8');
      
      OWA_UTIL.mime_header ('application/vnd.ms-excel',FALSE);  
      htp.print('Content-Disposition:attachment;filename='||v_filename);
      OWA_UTIL.Http_Header_Close;
      htp.p('<table border="1" style="width: 100%">');
      htp.p('<tr style="font-weight: bold; color: #0000FF">');
      htp.p('<td>');
      htp.p('TABLE_NAME');
      htp.p('</td>');
      htp.p('<td>');
      htp.p('COMMENTS');
      htp.p('</td>');
      htp.p('</tr>');
      for i in dictionary_tab.FIRST .. dictionary_tab.LAST loop
          htp.p('<tr>');
          htp.p('<td align="LEFT">'||dictionary_tab(i).table_name||'</td>');
          htp.p('<td align="LEFT">'||dictionary_tab(i).comments||'</td>');
          htp.p('</tr>');
      end loop;  
      htp.p('</table>');
      return;
   end if;
   
   htp.p('<center><font size="6">Download Oracle Dictionary to Excel File - Example</font><p>');
   htp.p('<font size="4"><i>Programmed by DanBrother</i></font><p>');   
   htp.formOpen('download_dictionary_to_excel',cattributes => 'target="_blank"'); 
   htp.formHidden('p_download',1);
   htp.formSubmit(null,'Download Dictionary to Excel',cattributes => 'style="background-color:#009900; color: #ffffff"');
   htp.formClose;
   htp.p('</center>');    
       
   exception
        when others then
             htp.p(dbms_utility.format_error_backtrace||' / '||sqlerrm);
end ;