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;
/