Send email in oracle 10g

Hi I have 2 tables and their outputs are as below

table1:
userID Email address
Rakesh rakesh.k@xyz.com
hari   hari.m@xyz.com
abc    abc@xyz.com


table2:

subject1
subject2
subject3

i want a query to send an email from abc(i login into my website as abc) to Rakesh or hari(based on my selection from a jsp) with subject as any one from table 2 and it should be appended with string "reallocated"

please help me tanks

For sending emails from database you need to have a procedure using UTIL_SMTP or some other email protocols. For UTIL_SMTP create a procedure as follows

    create or replace PROCEDURE send_mail (P_SENDER in varchar2,
                     P_RECIPIENT in varchar2,
                     P_CC in varchar2,
                     P_SUBJECT   in varchar2,
                     P_MESSAGE   in varchar2) is

mailhost varchar2(30) := 'xxx.xxx.xx.xx';
mail_conn utl_smtp.connection;
crlf varchar2(2) := CHR(13)||CHR(10);
mesg varchar2(4000);
BEGIN
  mail_conn := utl_smtp.open_connection(mailhost,25);
  mesg := 'Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss' )||crlf||
          'FROM: '||P_SENDER||'>'||crlf||'Subject: '||P_SUBJECT||crlf||
          'To: '||P_RECIPIENT||crlf||
          'Cc: '||P_CC||crlf||crlf||P_MESSAGE;
  utl_smtp.helo(mail_conn,mailhost);
  utl_smtp.mail(mail_conn,P_SENDER);
  utl_smtp.rcpt(mail_conn,P_RECIPIENT);
  utl_smtp.rcpt(mail_conn,P_CC);
  utl_smtp.data(mail_conn,mesg);
  utl_smtp.quit(mail_conn);
END send_mail;
/

You can get SMTP_HOST and SMTP_PORT from your System Administrator. SMTP_PORT will be 25 by default. Once you have that you could execute your procedure as

exec send_mail('abc@xyz.com',
            'rakesh.k@xyz.com',
            'hari.k@xyz.com',
            '<Your subject>',
            '<Your message>');

If you want to invoke your procedure for sending emails from JSP, you could call the procedure and pass the parameters from JSP to your procedure. If you want use a select statement for sending emails wrap your procedure in a function and execute function as