oracle 通過觸發器和預存程序寫檔案到磁碟

項目有一個需求，需要監控一張表的資料變動，然後根據變動結果產生一個檔案到磁碟便於日誌抓取程式進行監控。

需要用到oracle 內建的util_file 包。

目標資料庫使用者為ceps.

用sysdba登陸，為使用者賦權：
grant resource to ceps;
create or replace directory BATCHDIR as 'e:\ceps';
grant read,write on directory BATCHDIR to ceps;
GRANT EXECUTE ON utl_file TO ceps;
grant create procedure to ceps;
grant create trigger to ceps;

接著是需要用到的過程和觸發器

CREATE OR REPLACE PROCEDURE P_BATCHMON
AS
type ref_cursor_type is REF CURSOR;
cursor_select ref_cursor_type;
select_cname varchar2(1000);
v_file_handle utl_file.file_type;
v_sql varchar2(1000);
v_filepath Varchar2(500);
v_filename Varchar2(500);
v_results Varchar2(500);
v_pid varchar2(1000);
v_cpcnshortname Varchar2(500);
begin
v_filename:='batch_'|| substr(to_char(sysdate,'YYYYMMDD'),1,10) ||'.log' ;/*寫入的檔案名稱*/
select_cname:='select batch_id,batch_name from bfs_sys_batch_tasks where last_exec_stauts!=''00''';/*用於展示的結果集*/
v_file_handle:=utl_file.fopen('BATCHDIR',v_filename,'w');/*開啟寫入檔案。參數 W 表示覆蓋，O表示續寫*/
Open cursor_select For select_cname;
Fetch cursor_select into v_pid,v_cpcnshortname;
While cursor_select%Found
Loop
    v_results := v_pid||'|'||v_cpcnshortname||'BATCHERROR'||'\n';
    utl_file.put_line(v_file_handle,v_results);
  Fetch cursor_select into v_pid,v_cpcnshortname;
End Loop;
Close cursor_select;
utl_file.fClose(v_file_handle);
end P_BATCHMON;
/

create or replace trigger T_BATCHMON
after update on  bfs_sys_batch_tasks
begin
P_BATCHMON;
end;
/