email.sql

-- Load the following jar before send email
cd $ORACLE_HOME/plsql/jlib
loadjava -user sys/jsmith@smdsma -v -r -f plsql.jar

DECLARE
     c utl_smtp.connection;
  
     PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
     BEGIN
       utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
     END;
  
BEGIN
     c := utl_smtp.open_connection('proxy.seagate.com');
     utl_smtp.helo(c, 'seagate.com');
     utl_smtp.mail(c, 'jack.smith@seagate.com');
     utl_smtp.rcpt(c, 'jack.smith@seagate.com');
     utl_smtp.open_data(c);
     send_header('From',    '"Sender" <jack.smith@seagate.com>');
     send_header('To',      '"Recipient" <jack.smith@seagate.com>');
     send_header('Subject', 'Hello');
     utl_smtp.write_data(c, utl_tcp.CRLF || 'Test!');
     utl_smtp.close_data(c);
     utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
       utl_smtp.quit(c);
       raise_application_error(-20000,
         'Failed to send mail due to the following error: ' || sqlerrm);
END;
/


CREATE OR REPLACE PROCEDURE SP_Send_EMAIL (pis_server    IN VARCHAR2,
                                 pis_sender    IN VARCHAR2,
                                 pis_recipient IN VARCHAR2,
                                 pis_subject   IN VARCHAR2,
                                 pis_body      IN VARCHAR2)
IS
 c sys.utl_smtp.connection;
BEGIN

-- c := utl_smtp.open_connection('proxy.seagate.com');
c := utl_smtp.open_connection(pis_server);
   
     utl_smtp.helo(c, 'seagate.com');
     utl_smtp.mail(c, 'jack.smith@seagate.com');
     utl_smtp.rcpt(c, 'jack.smith@seagate.com');
     utl_smtp.open_data(c);
     
     --send_header('From',    '"Sender" <jack.smith@seagate.com>');
     utl_smtp.write_data(c, 'From' || ': ' ||pis_sender||'<'||pis_sender||'>'|| utl_tcp.CRLF);
     
     --send_header('To',      '"Recipient" <jack.smith@seagate.com>');
     utl_smtp.write_data(c, 'To' || ': ' ||'"Recipient" <'||pis_recipient||'>' || utl_tcp.CRLF);
     
     --send_header('Subject', 'Hello');
     utl_smtp.write_data(c, 'Subject' || ': ' || pis_subject || utl_tcp.CRLF);
     
     
     utl_smtp.write_data(c, utl_tcp.CRLF || pis_body);
     utl_smtp.close_data(c);
     utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
       utl_smtp.quit(c);
       raise_application_error(-20000,
         'Failed to send mail due to the following error: ' || sqlerrm);





END SP_SEND_EMAIL;
/          

exec JSMITH.SP_Send_EMAIL('proxy.seagate.com','jack.smith@seagate.com','jack.smith@seagate.com','Test!','Body!');