定時將表資料匯出到文字檔案的初步解決方案,求改進

myownstars發表於2010-08-11
任務描述:將資料庫中部分表匯出到外部檔案中,要求定時工作,伺服器上沒用安裝pl.sql developer之類的第三方軟體,所有操作只能透過job實現。
解決思路:先編寫一個procedure,用於生成一個sql檔案,該sql檔案內容大致為
         spool e:\file.csv  select * from table   spool off
   建立第一個job,每天定時執行該procedure,生成sql檔案;
 建立一個bat檔案, 呼叫上述sql檔案,內容為sqlplus kr23499/******@atoqcdb as sysdba @E:\dailyform\dailyreport.sql;
   然後建立第二個job,每天定時呼叫bat檔案,最終生成所需要的csv文字檔案;
   整個方案不需要藉助os的schedule task,10g推出的dbms_scheduler可以呼叫作業系統級別的檔案來建立job
大致程式碼:
    1. 首先是procedure的建立,主要運用utl_file包將產生的sql語句輸出到外部sql檔案,大致程式碼如下
create or replace procedure kr23499.dailyreport
is
  type c_cur is ref cursor;
  v_cur c_cur;
  v_col varchar2(50);
  v_type varchar2(50);
  v_pro varchar2(200);
  v_heading varchar2(4000);
  v_sql varchar2(4000);
  v_dir varchar2(200);
  v_file varchar2(40);
  v_cursql varchar2(4000);
  l_output utl_file.file_type;
begin
  l_output := utl_file.fopen('DAILYFORM','dailyreport.sql','W');

  utl_file.put_line(l_output,'set linesize 5000');
  utl_file.put_line(l_output,'set pagesize 0');
  utl_file.put_line(l_output,'set trimspool on');
  utl_file.put_line(l_output,'set colsep '',''');

v_heading := 'select ''cy_cycle_id'',''cy_cycle'',''cy_open_date'',''cy_close_date'',''cy_status'',''cy_cycle_ver_stamp'',''cy_attachment'',''cy_vts'', ''cy_os_config'',''cy_request_id'',''cy_task_status'',''cy_text_sync'',''cy_assign_rcyc''';
   v_sql := 'select cy_cycle_id,cy_cycle,cy_open_date,cy_close_date,cy_status,cy_cycle_ver_stamp,cy_attachment,cy_vts,cy_os_config,cy_request_id,cy_task_status,cy_text_sync,cy_assign_rcyc';
   v_cursql := 'select column_name,data_type from dba_tab_columns where table_name =''CYCLE'' and column_name like ''CY_USER_%'' and owner = '''||i.project||'''' ;

   open v_cur for v_cursql;
   loop
     fetch v_cur into v_col,v_type;
     exit when v_cur%notfound;

     if v_type != 'CLOB' then
       v_heading := v_heading ||','''||v_col||'''';
       v_sql := v_sql||','||v_col||'';
     end if;
   end loop;
   close v_cur;

   v_heading := v_heading||' from dual;';
   v_sql := v_sql||' from '||i.project||'.cycle;';

   utl_file.put_line(l_output,'spool E:\dailyform\'||i.project||'_cycle.csv');
   utl_file.put_line(l_output,v_heading);
   utl_file.put_line(l_output,v_sql);
   utl_file.put_line(l_output,'spool off');

utl_file.fclose(l_output);
end;
每次呼叫會生成一個sql指令碼檔案
set linesize 5000
set pagesize 0
set trimspool on
set colsep ','
spool E:\dailyform\APAC_APAC_CLUSTER_EBS_SIT_DB_cycle.csv
select 'cy_cycle_id','cy_cycle','cy_open_date','cy_close_date','cy_status','cy_cycle_ver_stamp','cy_attachment','cy_vts', 'cy_os_config','cy_request_id','cy_task_status','cy_text_sync','cy_assign_rcyc','CY_USER_01','CY_USER_02','CY_USER_03','CY_USER_04','CY_USER_05','CY_USER_06' from dual;
select cy_cycle_id,cy_cycle,cy_open_date,cy_close_date,cy_status,cy_cycle_ver_stamp,cy_attachment,cy_vts,cy_os_config,cy_request_id,cy_task_status,cy_text_sync,cy_assign_rcyc,CY_USER_01,CY_USER_02,CY_USER_03,CY_USER_04,CY_USER_05,CY_USER_06 from APAC_APAC_CLUSTER_EBS_SIT_DB.cycle;
spool off

2. 建立bat檔案 用來呼叫第一步生成的sql指令碼
sqlplus kr23499/******@atoqcdb as sysdba @E:\dailyform\dailyreport.sql;

3. 建立2個job
 第一個用來定時呼叫sql檔案,第二個定時呼叫bat檔案,兩個job執行時間應該有適當的時差
將建立第二個job的程式碼悉數列出
begin
  dbms_scheduler.create_program (
   program_name =>'dailydashboard_bat',
   program_type =>'EXECUTABLE',
   program_action => 'E:\dailyform\dailyreport.bat',
   enabled => TRUE,
   comments => 'to run the sql file and generate excel');
end;
begin
dbms_scheduler.create_schedule(
schedule_name => 'dailyreport_schedule_bat',
start_date => sysdate,
repeat_interval => 'FREQ=DAILY;BYHOUR =22;BYMINUTE =30',
comments => 'runs at 10:30 pm every day ');
end;
begin
dbms_scheduler.create_job(
job_name => 'dailyreport_sql_job',
program_name => 'dailydashboard_bat',
schedule_name => 'dailyreport_schedule_bat',
enabled => true);
end;

最後整個任務結束,不過感覺這樣似乎有點繞彎彎,有沒有一種更直接的方法可以實現上述功能的,即能不能略掉中間的bat檔案,或者只建立一個job便可以生成最終的csv檔案?  還望各位不吝賜教

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15480802/viewspace-688352/,如需轉載,請註明出處,否則將追究法律責任。

相關文章