ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 定时将表数据导出到文本文件的初步解决方案,求改进

定时将表数据导出到文本文件的初步解决方案,求改进

原创 Linux操作系统 作者:myownstars 时间:2010-08-11 18:18:11 0 删除 编辑
任务描述:将数据库中部分表导出到外部文件中,要求定时工作,服务器上没用安装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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3112192