I have this code that makes a csv with the alters in the dba_audit_object:
CREATE OR REPLACE PROCEDURE generate_audit_csv AS
v_output_dir VARCHAR2(4000) := 'UTL_FILE_DIR'; -- Utilizamos el nombre del directorio lógico
v_output_file VARCHAR2(4000) := 'action_found.csv';
v_file_handle UTL_FILE.FILE_TYPE;
v_execution_date DATE := SYSDATE;
-- Defined registries
TYPE audit_details_type IS RECORD (
os_username VARCHAR2(128),
username VARCHAR2(128),
terminal VARCHAR2(128),
owner VARCHAR2(128),
obj_name VARCHAR2(128),
action_name VARCHAR2(128),
timestamp DATE,
count_actions NUMBER
);
TYPE audit_details_table IS TABLE OF audit_details_type;
audit_details_list audit_details_table := audit_details_table();
BEGIN
-- Open file
v_file_handle := UTL_FILE.FOPEN(v_output_dir, v_output_file, 'W');
-- Iteration DBLINK one by one
FOR rec_link IN (SELECT db_link FROM user_db_links) LOOP
DECLARE
v_db_link_name VARCHAR2(128) := rec_link.db_link;
v_link_open BOOLEAN := FALSE;
BEGIN
-- Consulta para obtener los detalles del audit a través de nuestros DBLINKs
EXECUTE IMMEDIATE '
SELECT OS_USERNAME, USERNAME, TERMINAL, OWNER, OBJ_NAME, ACTION_NAME, MAX(TIMESTAMP), COUNT(*)
FROM DBA_AUDIT_OBJECT@'||v_db_link_name||'
WHERE TIMESTAMP > SYSDATE - 5
AND (
(ACTION_NAME IN (''SELECT'', ''UPDATE'', ''DELETE'', ''INSERT'')
AND UPPER(OWNER) NOT IN (
''ADMRMAN'',''ANONYMOUS'',''APEX_030200'',''APEX_PUBLIC_USER'',''APPQOSSYS'',
''CQADMIN'',''CQUSER'',''CSW_USR_ROLE'',''CTXSYS'',''DBA'',
''DBSNMP'',''DELETE_CATALOG_ROLE'',''DIP'',''DMSYS'',''EXFSYS'',''EXP_FULL_DATABASE'',''FLOWS_030000'',''FLOWS_FILES'',
''GATHER_SYSTEM_STATISTICS'',''IMP_FULL_DATABASE'',''MDDATA'',''MDSYS'',''MGMT_VIEW'',''OAS_PUBLIC'',''OAS_PUBLIC'',
''ODM'',''ODM_MTR'',''OLAPSYS'',''OLAP_XS_ADMIN'',''OLAP_USER'',''OLAP_DBA'',''ORACLE_OCM'',''ORAEG'',''ORDDATA'',''ORDPLUGINS'',
''ORDSYS'',''OUTLN'',''OWBSYS'',''OWBSYS_AUDIT'',''PERFSTAT'',''PUBLIC'',''QS'',''QS_ADM'',''QS_CB'',''QS_CBADM'',''QS_CS'',''QS_ES'',
''QS_OS'',''QS_WS'',''RHQADMIN'',''RHQADMIN'',''RMAN'',''SCOTT'',''SI_INFORMTN_SCHEMA'',''SPATIAL_CSW_ADMIN'',''SPATIAL_CSW_ADMIN_USR'',
''SPATIAL_WFS_ADMIN'',''SPATIAL_WFS_ADMIN_USR'',''SPOTLIGHT'',''SQLTXPLAIN'',''SYS'',''SYSMAN'',''SYSTEM'',''TOAD'',''TSMSYS'',''WFS_USR_ROLE'',
''WK_TEST'',''WKPROXY'',''WKSYS'',''WMSYS'',''XDB'',''XDBADMIN'',''XS$NULL'',''APEX_040200''
)
AND UPPER(TERMINAL) NOT IN (
''PGRVMQROPJPAP03'', ''PGRQROPJPAPP02'', ''PGRQROPJPAPP03'', ''PGRQROPJPAPP05'',
''PGRQROPJPAPP06'', ''PGRQROPJPAPP07'', ''PGRQROPJPAPP08'', ''PGRQROPJPAPP09'',
''PGRQROPJPAPP10'', ''PGRQROPJPAPP11'', ''PGRQROPJPAPP12'', ''PGRQROPJPAPP13'',
''PGRQROPJPAPP14'', ''PGRVMQROPJPAP01'', ''PGRVMQROPJPAP02'', ''A103IKP002.FGR.ORG.MX'',
''PGRQROPJPAPP18'', ''PGRQROPJPAPP16'', ''PGRQROPJPAPP17'', ''PGRQROPJPAPP19'',
''A813IGR02'', ''A103IKP003.FGR.ORG.MX'', ''PGRVMQROPJPAP02''
))
OR
(ACTION_NAME IN (''ALTER USER'', ''ALTER FUNCTION'', ''ALTER PROCEDURE'', ''ALTER DATABASE'', ''ALTER VIEW''))
)
GROUP BY OS_USERNAME, USERNAME, TERMINAL, OWNER, OBJ_NAME, ACTION_NAME'
BULK COLLECT INTO audit_details_list;
v_link_open := TRUE;
-- Write headers
IF audit_details_list.COUNT > 0 THEN
UTL_FILE.PUT_LINE(v_file_handle, 'DBLINK,DATE/TIME,Total actions audited');
UTL_FILE.PUT_LINE(v_file_handle, v_db_link_name || ',' || TO_CHAR(v_execution_date, 'DD-MON-YYYY HH24:MI:SS') || ',' || audit_details_list.COUNT);
-- Escribir los detalles del audit en el archivo CSV
UTL_FILE.PUT_LINE(v_file_handle, 'DBLINK,OS USERNAME,USERNAME,TERMINAL,OWNER,OBJ NAME,ACTION NAME,TIMESTAMP,COUNT ACTIONS');
FOR i IN 1..audit_details_list.COUNT LOOP
UTL_FILE.PUT_LINE(v_file_handle, v_db_link_name || ',' || audit_details_list(i).os_username || ',' || audit_details_list(i).username || ',' || audit_details_list(i).terminal || ',' ||
audit_details_list(i).owner || ',' || audit_details_list(i).obj_name || ',' || audit_details_list(i).action_name || ',' ||
TO_CHAR(audit_details_list(i).timestamp, 'YYYY-MM-DD HH24:MI:SS') || ',' || audit_details_list(i).count_actions);
END LOOP;
ELSE
UTL_FILE.PUT_LINE(v_file_handle, v_db_link_name || ',' || 'No audit data found.');
END IF;
COMMIT; s
-- Close DBLINK
IF v_link_open THEN
DBMS_SESSION.CLOSE_DATABASE_LINK(v_db_link_name);
v_link_open := FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF v_link_open THEN
EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK(' || v_db_link_name || '); END;';
v_link_open := FALSE;
END IF;
DBMS_OUTPUT.PUT_LINE('Error processing DBLINK ' || v_db_link_name || ': ' || SQLERRM);
END;
END LOOP;
-- Close Output CSV file
UTL_FILE.FCLOSE(v_file_handle);
DBMS_OUTPUT.PUT_LINE('CSV file created: ' || v_output_file);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(v_file_handle) THEN
UTL_FILE.FCLOSE(v_file_handle);
END IF;
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END generate_audit_csv;
/
SHOW ERRORS;
this works perfectly, now I am trying to send the csv via email.
this is my approach, it doesn't work:
PROCEDURE generar_y_enviar_csv(
v_from IN VARCHAR2,
v_to IN VARCHAR2,
v_asunto IN VARCHAR2,
v_firma IN VARCHAR2,
v_cc IN VARCHAR2 DEFAULT '',
v_filename IN VARCHAR2 DEFAULT 'action_found.csv',
v_directorio IN VARCHAR2 DEFAULT 'UTL_FILE_DIR'
) IS
vg_contenido CLOB := EMPTY_CLOB();
file_handle UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount INTEGER;
BEGIN
-- Abrir el archivo
file_handle := UTL_FILE.FOPEN(v_directorio, v_filename, 'R');
-- Leer el contenido del archivo en el cuerpo del correo
LOOP
UTL_FILE.GET_LINE(file_handle, buffer);
amount := LENGTH(buffer);
IF amount > 0 THEN
IF vg_contenido IS NULL THEN
vg_contenido := buffer;
ELSE
vg_contenido := vg_contenido || CHR(10) || buffer;
END IF;
END IF;
END LOOP;
-- Cerrar el archivo
UTL_FILE.FCLOSE(file_handle);
-- Enviar el correo electrónico con el archivo adjunto y el contenido en el cuerpo
PG_ENVIO_MAIL.entrega(
vg_from => v_from,
vg_to => v_to,
vg_asunto => v_asunto,
vg_cuerpo => vg_contenido,
vg_cc => v_cc,
vg_filename => v_filename,
vg_firma => v_firma
);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.FCLOSE(file_handle);
END IF;
DBMS_OUTPUT.PUT_LINE('Error al procesar el archivo: ' || SQLERRM);
END generar_y_enviar_csv;
END PG_GENERA_Y_ENVIA_CSV;
/
I have a package which I used, that sends mail using UTL_SMTP:
CREATE OR REPLACE PACKAGE UEITICDSCC_FRL_SS.PG_ENVIO_MAIL AS
PROCEDURE send_header
(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2, payload IN VARCHAR2);
PROCEDURE send_subject (conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2, payload IN VARCHAR2);
PROCEDURE mailit(
vg_from in Varchar,
vg_to IN VARCHAR2,
vg_asunto IN VARCHAR2,
vg_msg IN VARCHAR2,
vg_cc IN varchar2 default '',
vg_filename IN VARCHAR2,
vg_contenido IN CLOB);
FUNCTION ConvierteAcentos (cadena1 IN VARCHAR2,tip IN NUMBER) RETURN VARCHAR2;
PROCEDURE entrega(vg_from IN VARCHAR2,
vg_to IN VARCHAR2,
vg_asunto IN VARCHAR2,
vg_cuerpo IN CLOB,
vg_firma IN VARCHAR2,
vg_cc IN varchar2 default '',
vg_filename IN varchar2 default '',
vg_contenido IN CLOB default '');
END PG_ENVIO_MAIL;
/
show errors;
CREATE OR REPLACE PACKAGE BODY UEITICDSCC_FRL_SS.PG_ENVIO_MAIL IS
PROCEDURE send_header
(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2, payload IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(
conn,
name || ': ' ||
utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(payload))||
''||
utl_tcp.crlf);
END send_header;
PROCEDURE send_subject
(conn IN OUT NOCOPY utl_smtp.connection,
name IN VARCHAR2, payload IN VARCHAR2) IS
BEGIN
utl_smtp.write_data(
conn,
--name || ': =?ISO-8859-1?B?' ||
--payload ||'?='||utl_tcp.crlf);
-- lineas comentada por VHO el 30/03/12, la sustituye la siguiente
name || ':' || utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(payload)) || ' ' ||
utl_tcp.crlf);
END send_subject;
PROCEDURE mailit(
vg_from in Varchar,
vg_to IN VARCHAR2,
vg_asunto IN VARCHAR2,
vg_msg IN VARCHAR2,
vg_cc IN varchar2 default '',
vg_filename IN varchar2,
vg_contenido IN CLOB)
AS
conn utl_smtp.connection;
vl_mailhost varchar2(1000) := 'webmail.pgr.gob.mx';
vl_port number := 25;
vl_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
vl_step PLS_INTEGER := 24573;
vl_clob CLOB := vg_contenido;
v_len INTEGER;
v_index INTEGER;
v_chunk VARCHAR2(32000);
BEGIN
conn := utl_smtp.open_connection(vl_mailhost, vl_port);
utl_smtp.helo(conn, vl_mailhost);
utl_smtp.mail(conn, vg_from);
utl_smtp.rcpt(conn, vg_to);
IF vg_cc is not null THEN
utl_smtp.rcpt(conn, vg_cc);
END IF;
utl_smtp.open_data(conn);
send_header(conn, 'To', vg_to);
send_header(conn, 'From', vg_from);
send_subject(conn, 'Subject', vg_asunto);
IF vg_cc is not null THEN
send_header(conn, 'To', vg_cc);
END IF;
utl_smtp.write_data(conn, 'MIME-Version: 1.0' || utl_tcp.CRLF);
-- utl_smtp.write_data(conn, 'Content-Type: text/html; charset=UTF-8' || utl_tcp.CRLF);
-- utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Type: multipart/mixed; boundary="' || vl_boundary || '"' || utl_tcp.CRLF || utl_tcp.CRLF);
IF vg_msg IS NOT NULL THEN
utl_smtp.write_data(conn, '--' || vl_boundary || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Type: text/html; charset=UTF-8' || utl_tcp.CRLF || utl_tcp.CRLF);
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(vg_msg));
utl_smtp.write_data(conn, utl_tcp.CRLF || utl_tcp.CRLF);
END IF;
IF vg_filename IS NOT NULL THEN
utl_smtp.write_data(conn, '--' || vl_boundary || utl_tcp.crlf);
utl_smtp.write_data(conn, 'Content-Type: text/plain; charset="UTF-8" name="' || vg_filename || '"' || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' || utl_tcp.CRLF);
utl_smtp.write_data(conn, 'Content-Disposition: attachment; filename="' || vg_filename || '"' || utl_tcp.CRLF || utl_tcp.CRLF);
IF vg_contenido IS NOT NULL THEN
vl_clob := ConvierteAcentos(vg_contenido,2);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength( vl_clob) - 1 )/vl_step) LOOP
utl_smtp.write_data(conn, DBMS_LOB.substr( vl_clob, vl_step, i * vl_step + 1));
END LOOP;
END IF;
utl_smtp.write_data(conn, utl_tcp.CRLF || utl_tcp.CRLF);
END IF;
IF vg_filename IS NOT NULL AND vg_contenido IS NOT NULL THEN
utl_smtp.write_data(conn, '--' || vl_boundary || UTL_TCP.CRLF);
utl_smtp.write_data(conn, 'Content-Type: text/plain; charset="UTF-8" name="' || vg_filename || '"' || UTL_TCP.CRLF);
utl_smtp.write_data(conn, 'Content-Transfer-Encoding: 8bit' || UTL_TCP.CRLF);
utl_smtp.write_data(conn, 'Content-Disposition: attachment; filename="' || vg_filename || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF);
v_len := DBMS_LOB.getlength(vl_clob);
WHILE v_index <= v_len LOOP
v_chunk := DBMS_LOB.SUBSTR(vl_clob, 32000, v_index);
utl_smtp.write_data(conn, v_chunk);
v_index := v_index + 32000;
END LOOP;
utl_smtp.write_data(conn, UTL_TCP.CRLF || UTL_TCP.CRLF);
END IF;
utl_smtp.write_data(conn, '--' || vl_boundary || '--' || utl_tcp.crlf);
-- utl_smtp.write_data(conn, utl_tcp.crlf);
-- utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(vg_msg));
-- utl_smtp.write_data(conn, utl_tcp.CRLF || utl_tcp.CRLF);
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN null;
WHEN others then null;
END;
raise_application_error(-20000,'Failed to send mail due to the following error:'||sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20000,'Failed to send mail due to the following error:'||sqlerrm);
END mailit;
-----------------
FUNCTION ConvierteAcentos (cadena1 IN VARCHAR2, tip IN NUMBER) RETURN VARCHAR2 IS
cadena VARCHAR2(2000);
i number;
liValorAscii number;
letra varchar2(50);
resultado varchar2(4000);
ant varchar2(50);
post varchar2(50);
paso varchar2(50);
BEGIN
resultado:='';
cadena:=cadena1;
FOR i IN 1 .. LENGTH(cadena) LOOP
letra := SUBSTR(cadena, i, 1);
liValorAscii := ASCII(SUBSTR(cadena, i, 1));
ant := ASCII(SUBSTR(cadena, i-1, 1));
post := ASCII(SUBSTR(cadena, i+1, 1));
CASE
WHEN liValorAscii in (181,193,230) then IF(tip=1) THEN letra:=''||chr(38)||'Aacute;'; ELSE letra:=''||chr(38)||'A'; END IF;
WHEN liValorAscii in (160,225,255) then IF(tip=1) THEN letra:=''||chr(38)||'aacute;'; ELSE letra:=''||chr(38)||'a'; END IF;
WHEN liValorAscii in (144) then IF(tip=1) THEN letra:=''||chr(38)||'Eacute;'; ELSE letra:=''||chr(38)||'E'; END IF;
WHEN liValorAscii in (130,254) then IF(tip=1) THEN letra:=''||chr(38)||'eacute;'; ELSE letra:=''||chr(38)||'e'; END IF;
WHEN liValorAscii in (214,153) then IF(tip=1) THEN letra:=''||chr(38)||'Iacute;'; ELSE letra:=''||chr(38)||'I'; END IF;
WHEN liValorAscii in (161,173) then IF(tip=1) THEN letra:=''||chr(38)||'iacute;'; ELSE letra:=''||chr(38)||'i'; END IF;
WHEN liValorAscii in (224,133) then IF(tip=1) THEN letra:=''||chr(38)||'Oacute;'; ELSE letra:=''||chr(38)||'O'; END IF;
WHEN liValorAscii in (162,189) then IF(tip=1) THEN letra:=''||chr(38)||'oacute;'; ELSE letra:=''||chr(38)||'o'; END IF;
WHEN liValorAscii in (233,130) then IF(tip=1) THEN letra:=''||chr(38)||'Uacute;'; ELSE letra:=''||chr(38)||'U'; END IF;
WHEN liValorAscii in (163,156) then IF(tip=1) THEN letra:=''||chr(38)||'uacute;'; ELSE letra:=''||chr(38)||'u'; END IF;
WHEN liValorAscii in (165,190) then IF(tip=1) THEN letra:=''||chr(38)||'Ntilde;'; ELSE letra:=''||chr(38)||'�'; END IF;
WHEN liValorAscii in (164,207) then IF(tip=1) THEN letra:=''||chr(38)||'ntilde;'; ELSE letra:=''||chr(38)||'�'; END IF;
WHEN liValorAscii in (233,154) then letra:=''||chr(38)||'Uuml;';
WHEN liValorAscii in (129) then letra:=''||chr(38)||'Uuml;';
ELSE liValorAscii:=liValorAscii;
END CASE;
IF (liValorAscii not in (117,85)) THEN
IF (ant=103) THEN
IF (post=105 OR post=101) THEN letra:=''||chr(38)||'uuml;';
END IF;
IF (ant=71 OR ant=254) THEN
IF (post=69 OR post=73) THEN letra:=''||chr(38)||'Uuml;';
end if;
END IF;
END IF;
end if;
resultado:=resultado||letra;
END LOOP;
RETURN resultado;
END;
-----------------
PROCEDURE entrega(vg_from IN VARCHAR2,
vg_to IN VARCHAR2,
vg_asunto IN VARCHAR2,
vg_cuerpo IN CLOB,
vg_firma IN VARCHAR2,
vg_cc IN varchar2 default '',
vg_filename IN varchar2 default '',
vg_contenido IN CLOB default ''
) AS
BEGIN
DECLARE
unicodedb number;
vl_subject varchar2(2000);
final_hex_train varchar2(2000);
vl_html CLOB;
vl_firma VARCHAR2(2000);
vl_cuerpo VARCHAR2(2000);
vl_contenido CLOB;
BEGIN
-- determine if connected to Unicode DB (return 1)
-- or not (return 0)
select
decode(value,
-- Unicode Charactersets
'AL24UTFFSS',1,
'UTFE',1,
'UTF8',1,
'AL32UTF8',1,
'AL16UTF16',1,
-- NON-Unicode characterset
0 )
into
unicodedb
from
nls_database_parameters
where
upper(parameter)='NLS_CHARACTERSET';
vl_cuerpo:= ConvierteAcentos(vg_cuerpo,1);
vl_cuerpo := replace(vl_cuerpo, '+', '<br>');
vl_firma := ConvierteAcentos(vg_firma,1);
vl_firma := replace(vl_firma, '+' , '<br>');
vl_html := '<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"/>';
vl_html := vl_html || '</head><body><i> ' || vl_cuerpo || '<i><br><i><i><br><i><i><br><i>Atentamente <i><br><i>' || vl_firma || '<i><br></body></html>';
vl_subject := ConvierteAcentos(vg_asunto,2);
mailit(vg_from,vg_to,vl_subject,vl_html,vg_cc,vg_filename,vg_contenido);
END;
END entrega;
END PG_ENVIO_MAIL;
/
show errors
What am I doing wrong?
Do I need to modify the package?
or Do I need another approach?
I tried writing the csv as suggested by @PaulW, the result was this:
SET SERVEROUTPUT ON
DECLARE
v_sql VARCHAR2(4000);
v_connection UTL_SMTP.connection;
c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC0123456789DE';
v_clob CLOB := EMPTY_CLOB();
v_len INTEGER;
v_index INTEGER;
v_execution_date DATE := SYSDATE;
v_db_link_name VARCHAR2(128);
v_link_open BOOLEAN := FALSE;
-- We define a type of registry to store audit details.
TYPE audit_details_type IS RECORD (
os_username VARCHAR2(128),
username VARCHAR2(128),
terminal VARCHAR2(128),
owner VARCHAR2(128),
obj_name VARCHAR2(128),
action_name VARCHAR2(128),
timestamp DATE,
count_actions NUMBER
);
-- Define the table
TYPE audit_details_table IS TABLE OF audit_details_type;
-- Declare a variable
audit_details_list audit_details_table := audit_details_table();
BEGIN
FOR rec_link IN (
SELECT db_link
FROM user_db_links
) LOOP
v_db_link_name := rec_link.db_link;
v_link_open := FALSE;
BEGIN
-- Build the dynamic query
v_sql := 'SELECT OS_USERNAME, USERNAME, TERMINAL, OWNER, OBJ_NAME, ACTION_NAME, MAX(TIMESTAMP), COUNT(*)
FROM DBA_AUDIT_OBJECT@'||v_db_link_name||'
WHERE TIMESTAMP > SYSDATE - 5
AND (
(ACTION_NAME IN (''SELECT'', ''UPDATE'', ''DELETE'', ''INSERT'')
AND UPPER(OWNER) NOT IN (
''FILTER_OWNER''
)
AND UPPER(TERMINAL) NOT IN (
''FILTER_APP_SERVERS''
))
OR
(ACTION_NAME IN (''ALTER USER'', ''ALTER FUNCTION'', ''ALTER PROCEDURE'', ''ALTER DATABASE'', ''ALTER VIEW''))
)
GROUP BY OS_USERNAME, USERNAME, TERMINAL, OWNER, OBJ_NAME, ACTION_NAME';
-- Execute dynamic query
EXECUTE IMMEDIATE v_sql
BULK COLLECT INTO audit_details_list;
v_link_open := TRUE;
-- Print headers
IF audit_details_list.COUNT > 0 THEN
v_clob :=
v_clob
|| 'DBLINK_NAME,OS_USERNAME,USERNAME,TERMINAL,OWNER,OBJ_NAME,ACTION_NAME,TIMESTAMP'
|| UTL_TCP.crlf;
-- Build csv contents
FOR i IN 1..audit_details_list.COUNT LOOP
v_clob :=
v_clob
|| v_db_link_name || ',' -- Aquí agregamos el nombre del DBLINK
|| audit_details_list(i).os_username || ','
|| audit_details_list(i).username || ','
|| audit_details_list(i).terminal || ','
|| audit_details_list(i).owner || ','
|| audit_details_list(i).obj_name || ','
|| audit_details_list(i).action_name || ','
|| TO_CHAR(audit_details_list(i).timestamp, 'YYYY-MM-DD HH24:MI:SS')
|| UTL_TCP.crlf;
END LOOP;
ELSE
v_clob :=
v_clob
|| v_db_link_name || ','
||'No audit data found.'
|| UTL_TCP.crlf;
END IF;
COMMIT;
-- Close database link
IF v_link_open THEN
DBMS_SESSION.CLOSE_DATABASE_LINK(rec_link.db_link);
v_link_open := FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF v_link_open THEN
EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK('||v_db_link_name||'); END;';
END IF;
DBMS_OUTPUT.PUT_LINE('Error processing DBLINK ' || v_db_link_name || ': ' || SQLERRM);
END;
END LOOP;
-- Connect to server.
v_connection := UTL_SMTP.open_connection('smtp_server', 'port_number');
UTL_SMTP.helo(v_connection, 'yourdomain.com');
UTL_SMTP.mail(v_connection, 'sender@mail.com');
UTL_SMTP.rcpt(v_connection, 'recipient@mail.com');
UTL_SMTP.open_data(v_connection);
UTL_SMTP.write_data(v_connection, 'From: ' || 'sender@mail.com || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'To: ' || 'recipient@mail.com' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Subject: Audit Monitoring' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(
v_connection,
'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(
v_connection,
'This is a multi-part message in MIME format.' || UTL_TCP.crlf
);
UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);
UTL_SMTP.write_data(
v_connection,
'Content-Disposition: attachment; filename="'
|| 'audit_details.csv'
|| '"'
|| UTL_TCP.crlf
);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
v_len := DBMS_LOB.getlength(v_clob);
v_index := 1;
WHILE v_index <= v_len LOOP
UTL_SMTP.write_data(v_connection, DBMS_LOB.substr(v_clob, 32000, v_index));
v_index := v_index + 32000;
END LOOP;
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(v_connection);
UTL_SMTP.quit(v_connection);
EXCEPTION
WHEN OTHERS THEN
-- Manage errors with SMTP.
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;
/
I used this website: MAIL_CSV.