Oracle writes files to disks through triggers and stored procedures

A project needs to monitor data changes in a table, and then generate a file to the disk based on the change results to facilitate monitoring by log capturing programs.

You need to use the util_file package that comes with Oracle.

The target database user is Ceps.

Use sysdba to log on and grant permissions to users:
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;

Next is the process and trigger to be used.

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'),) |'. log';/* Name of the written file */
Select_cname: = 'select batch_id, batch_name from bfs_sys_batch_tasks where last_exec_stauts! = '00';/* result set for display */
V_file_handle: = utl_file.fopen ('batchdir', v_filename, 'w');/* Open the Write File. The W parameter indicates overwrite, and the O parameter indicates continued write */
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;
/