Send an email (with attachement) using Oracle PL/SQL
create or replace procedure SEND_EMAIL_WITH_TXT_ATTACH (
PVA_SENDER varchar2,
PVA_RECEIVER varchar2,
PVA_SUBJECT varchar2,
PVA_MESSAGE varchar2,
PVA_TEXT_FILE varchar2,
PVA_DIRECTORY varchar2 /* Database Object */ ) is
vfi_fileHandle utl_file.file_type;
vnu_file_len NUMBER;
vnu_file_blk_size NUMBER;
vbo_file_exists BOOLEAN;
vva_textLine VARCHAR2 (32000);
vva_file_text_toSend varchar2(32000);
vva_debug_ind varchar2(3);
begin
UTL_FILE.fgetattr (PVA_DIRECTORY, PVA_TEXT_FILE, vbo_file_exists, vnu_file_len, vnu_file_blk_size);
if vbo_file_exists then
vfi_fileHandle := UTL_FILE.fopen (PVA_DIRECTORY, PVA_TEXT_FILE, ‘R’);
end if;
LOOP
BEGIN
UTL_FILE.get_line (vfi_fileHandle, vva_textLine);
vva_file_text_toSend := vva_file_text_toSend || vva_textLine || UTL_TCP.crlf;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
UTL_FILE.fclose (vfi_fileHandle);
UTL_MAIL.SEND_ATTACH_VARCHAR2
(sender => PVA_SENDER,
recipients => PVA_RECEIVER,
subject => PVA_SUBJECT,
message => PVA_MESSAGE,
att_inline => false,
att_mime_type => ‘text/plain; charset=us-ascii’,
attachment => vva_file_text_toSend,
att_filename => PVA_TEXT_FILE ); –attached filename
UTL_FILE.fclose (vfi_fileHandle);
exception
when others then
raise;
end;
