定時將表資料匯出到文字檔案的初步解決方案,求改進
任務描述:將資料庫中部分表匯出到外部檔案中,要求定時工作,伺服器上沒用安裝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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何將資料熱匯出到檔案
- 採用sqlldr定時將文字檔案載入進入資料庫SQL資料庫
- 大文字資料,匯入匯出到資料庫資料庫
- 將資料匯出到ExcelExcel
- 將MYSQL中資料匯出到EXCEL表MySqlExcel
- 通過mysqlimport定時將遠端文字檔案匯入mysqlMySqlImport
- 將dataGridView內容匯出到Excel檔案ViewExcel
- MySQL 將查詢結果匯出到檔案MySql
- 將altibase記憶體庫的表匯出到oracle資料庫記憶體Oracle資料庫
- 從hive將資料匯出到mysql(轉)HiveMySql
- 臨時表空間資料檔案損壞的解決
- 阿里雲數倉Dataworks資料匯出到檔案step by step阿里
- 將informix匯出的文字資料匯入oracle資料庫ORMOracle資料庫
- 用SQL Loader將Excel資料匯出到OracleSQLExcelOracle
- 將Buffer Cache內容強制寫出到資料檔案
- Oracle工具之sqlldr的使用--如何將文字檔案或Excel中的資料匯入資料庫OracleSQLExcel資料庫
- 教你如何將二進位制檔案匯入到資料庫資料庫
- MSSQL資料匯出到MYSQLMySql
- 乾貨:blob匯出檔案亂碼解決方案
- 【FLUSH】將Buffer Cache內容強制寫出到資料檔案
- 定時將資料匯入到hive的shell指令碼Hive指令碼
- 某省發改委資料安全解決方案
- 一次將資料匯出為 CSV 格式檔案時遇到的坑
- Oracke大欄位Blob匯出到檔案
- 將企業檔案共享解決方案與資料丟失防護配對
- MSSQL表資料匯出成Insert指令碼<改進版>SQL指令碼
- 資料庫回檔解決方案資料庫
- C語言將資料表輸出到終端C語言
- 轉載:利用SQL*Loader將 Excel 資料匯出到資料庫中SQLExcel資料庫
- 前端axios請求二進位制資料流轉換生成PDF檔案空白問題(終極解決方案)前端iOS
- sqlserver匯入匯出文字檔案SQLServer
- oracle匯出到4G時提示終止寫入DMP檔案Oracle
- excel檔案內容匯入資料庫的問題及解決Excel資料庫
- 【實戰教程】使用雲函式將資料表匯出為 Excel 檔案函式Excel
- 一次資料庫匯入解決方案資料庫
- axios請求超時解決方案iOS
- 將命令列提示符裡的執行結果匯出到text檔案中命令列
- vfp匯入文字檔案