任务描述:将数据库中部分表导出到外部文件中,要求定时工作,服务器上没用安装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文件? 还望各位不吝赐教
解决思路:先编写一个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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-688352/