How External Table Works in StandBy Database

Standby database/Data Guard is highly used for reporting purposes. However, when you want to write some log data for your reporting purpose, you might encounter an issue that you are not allowed to do so on the standby instance.

In this case, using External Table is an option. You can create a stored procedure and use it to write os file for the external table.

Here is a sample code.

PROCEDURE wrt_transaction_log (p_job_id IN NUMBER,
p_programname IN VARCHAR2,
p_filename IN VARCHAR2,
p_full IN VARCHAR2,
p_feedstartts IN DATE,
p_feedendts IN DATE,
p_status IN VARCHAR2,
p_message IN VARCHAR2)
IS
v_output_file UTL_FILE.file_type;
filename VARCHAR2 (100);
v_string VARCHAR2 (2000);
nodata EXCEPTION;
BEGIN
EXECUTE IMMEDIATE ('ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''');

filename := 'at_genpdfile_pkg_log_ext.txt';
v_output_file := UTL_FILE.fopen ('XTABLES', filename, 'A');

v_string :=
at_genpdfile_pkg_log_seq.NEXTVAL
|| '|'
|| p_job_id
|| '|'
|| USER
|| '|'
|| p_programname
|| '|'
|| p_filename
|| '|'
|| p_full
|| '|'
|| p_feedstartts
|| '|'
|| p_feedendts
|| '|'
|| p_status
|| '|'
|| p_message
|| '|'
|| SYSDATE;
UTL_FILE.put_line (v_output_file, v_string);
UTL_FILE.fclose_all;
END;