As you probably know, Oracle 10g XE database does not provide the utl_mail package. So, in order to send e-mails from the database, you would have to use utl_smtp and write your own mail package. I found various solutions on the web but I had a major problem. Greek language in the mail subject and text. So I have made some adaptations to the code to support UTF-8 'message' and 'subject' text.

There are two procedures in the package, 'mail' for regular e-mail and 'mail_att' for attaching a file to the e-mail. Note that you have to change the package parameters according to your domain, sender, etc. and create a database directory (in my case, 'MY_DATABASE_DIR') where the file to be attached is residing.

Also, execution grants to the database user are needed: for package 'utl_smtp', 'utl_file', read and write permissions for the database directory and in case you are using Linux, the file to be attached must have 777 permissions and belong to 'oracle:dba'.

create or replace package mail_pkg is
g_smtp_host varchar2 (256) := 'localhost';
g_smtp_port pls_integer := 1925;
g_smtp_domain varchar2 (256) := 'example.com';
g_sender varchar2(256) := 'youremail_at_example.com';
g_passwd varchar2(256) := 'yourpassword';
g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
-- send mail using UTL_SMTP
procedure mail (p_recipient in varchar2, p_subject in varchar2, p_message in varchar2);

procedure mail_att (p_recipient in varchar2, p_subject in varchar2, p_message in varchar2, p_filename in varchar2 );

end;
/
create or replace package body mail_pkg is

procedure write_mime_header (p_conn in out nocopy utl_smtp.connection, p_name in varchar2, p_value in varchar2) is
begin
utl_smtp.write_data ( p_conn, p_name || ': ' || p_value || utl_tcp.crlf);
end write_mime_header;

procedure mail (p_recipient in varchar2, p_subject in varchar2, p_message in varchar2) is
l_conn utl_smtp.connection;
nls_charset varchar2(255) := 'AL32UTF8';
begin
-- establish connection and autheticate
l_conn := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
utl_smtp.ehlo(l_conn, g_smtp_domain);
utl_smtp.command(l_conn, 'auth login');
utl_smtp.command(l_conn,utl_encode.text_encode(g_sender, nls_charset, 1));
utl_smtp.command(l_conn, utl_encode.text_encode(g_passwd, nls_charset, 1));
-- set from/recipient
utl_smtp.command(l_conn, 'MAIL FROM: <'||g_sender||'>');
utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
-- write mime headers
utl_smtp.open_data (l_conn);
write_mime_header (l_conn, 'From', g_sender);
write_mime_header (l_conn, 'To', p_recipient);
utl_smtp.write_data(l_conn, 'Subject: =?UTF-8?Q?' || 
replace(utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(p_subject))), '=' || chr(13) || chr(10), '')
|| '?=' || utl_tcp.crlf); 
write_mime_header (l_conn, 'Content-Type', 'text/html; charset=UTF-8');
utl_smtp.write_data(l_conn, 'Content-Transfer-Encoding: quoted-printable '|| utl_tcp.crlf); 
write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
utl_smtp.write_data (l_conn, utl_tcp.crlf);
-- write message body
utl_smtp.write_raw_data (l_conn, utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(p_message)));
utl_smtp.close_data (l_conn);
-- end connection
utl_smtp.quit (l_conn);
exception
when others
then
begin
utl_smtp.quit(l_conn);
exception
when others then
null;
end;
raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);
end mail;

procedure mail_att (p_recipient in varchar2, p_subject in varchar2, p_message in varchar2, p_filename in varchar2 ) is
l_conn utl_smtp.connection;
l_file_handle utl_file.file_type; 
l_filename varchar2(1000);
l_line varchar2(1000);
l_mesg varchar2(32767);
nls_charset varchar2(255) := 'AL32UTF8';
begin
-- establish connection and autheticate
l_conn := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
utl_smtp.ehlo(l_conn, g_smtp_domain);
utl_smtp.command(l_conn, 'auth login');
utl_smtp.command(l_conn,utl_encode.text_encode(g_sender, nls_charset, 1));
utl_smtp.command(l_conn, utl_encode.text_encode(g_passwd, nls_charset, 1));
-- set from/recipient
utl_smtp.command(l_conn, 'MAIL FROM: <'||g_sender||'>');
utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
-- write mime headers
utl_smtp.open_data (l_conn);
write_mime_header (l_conn, 'From', g_sender);
write_mime_header (l_conn, 'To', p_recipient);
utl_smtp.write_data(l_conn, 'Subject: =?UTF-8?Q?' || 
replace(utl_raw.cast_to_varchar2(utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(p_subject))), '=' || chr(13) || chr(10), '')
|| '?=' || utl_tcp.crlf); 
write_mime_header (l_conn, 'Content-Type', 'multipart/mixed; boundary="DMW.Boundary.605592468"'); 
utl_smtp.write_data (l_conn, 'mime message content'||utl_tcp.crlf);
utl_smtp.write_data (l_conn, '--DMW.Boundary.605592468'||utl_tcp.crlf); 
write_mime_header (l_conn, 'Content-Type', 'text/html; charset=UTF-8');
utl_smtp.write_data(l_conn, 'Content-Transfer-Encoding: quoted-printable '|| utl_tcp.crlf); 
write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
utl_smtp.write_data (l_conn, utl_tcp.crlf);
-- write message body
utl_smtp.write_raw_data (l_conn, utl_encode.quoted_printable_encode(utl_raw.cast_to_raw(p_message)));
utl_smtp.write_data (l_conn, utl_tcp.crlf);
-- write attachment
l_file_handle := utl_file.fopen('MY_DATABASE_DIR', l_filename, 'R');
utl_smtp.write_data(l_conn, '--DMW.Boundary.605592468'||utl_tcp.crlf);
utl_smtp.write_data(l_conn, 'Content-Type: application/octet-stream; name="'||l_filename||'"'||utl_tcp.crlf);
utl_smtp.write_data(l_conn, 'Content-Disposition: attachment; filename="'||l_filename||'"'||utl_tcp.crlf); 
utl_smtp.write_data(l_conn, 'Content-Transfer-Encoding: 7bit'||utl_tcp.crlf);
utl_smtp.write_data (l_conn, utl_tcp.crlf);

begin
loop
utl_file.get_line(l_file_handle, l_line);
utl_smtp.write_data(l_conn, l_line||utl_tcp.crlf);
end loop;
exception
when no_data_found then
null;
end;

utl_smtp.write_data (l_conn, utl_tcp.crlf);
utl_file.fclose(l_file_handle);
-- end connection
utl_smtp.close_data (l_conn);
utl_smtp.quit (l_conn);
exception
when others
then
begin
utl_smtp.quit(l_conn);
exception
when others then
null;
end;
raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);
end mail_att;

end;