Sending Email using ORACLE procedure/package

If developers are working on large batch-processes of Oracle, then one common issue is 'alert' in case of any discrepancy.



Sending email through Oracle is very easy as you can do it through oracle's supplied package: utl_tcp


Note: Following code is without exception handling, which mean that we are not checking status of each TCP command that we are issuing through Oracle package. For this I will write sometimes latter. Assuming that everything will go fine:


This code is pretty much self explanatory.

CREATE OR REPLACE PROCEDURE send_simple_email(
          sender VARCHAR2,
          recipient VARCHAR2,
          message VARCHAR2
)
IS
          mailhost          VARCHAR2(300) := 'smtp.code4coder.com'; --Change it with your SMTP server.

          smtp_error     EXCEPTION;
          mail_conn      utl_tcp.connection;
          val                 PLS_INTEGER;

BEGIN
          mail_conn := utl_tcp.open_connection(remote_host => mailhost, remote_port => 25, charset => 'US7ASCII');
          val := utl_tcp.write_line(mail_conn, 'HELO ' || mailhost);
          val := utl_tcp.write_line(mail_conn, 'MAIL FROM: ' || sender);
          val := utl_tcp.write_line(mail_conn, 'RCPT TO: ' || recipient);
          val := utl_tcp.write_line(mail_conn, 'DATA');
          val := utl_tcp.write_line(mail_conn, message);
          val := utl_tcp.write_line(mail_conn, 'QUIT');
          utl_tcp.close_connection(mail_conn);

EXCEPTION
          WHEN OTHERS THEN
                    utl_tcp.close_connection(mail_conn);

END send_simple_email;

/

And to Call this procedure:

DECLARE
          SENDER VARCHAR2(200);
          RECIPIENT VARCHAR2(200);
          MESSAGE VARCHAR2(200);

BEGIN
          SENDER := 'email_through_oracle@coder4coder.com';
          RECIPIENT := 'everyOne@code4coder.com';
          MESSAGE := 'On Code4Coder.Com you will find different solutions/tips/tricks for better code. Targetted areas are ASP.Net, C#, VB.Net, PL/SQL, TSQL.';
     
          PROMO.SEND_SIMPLE_EMAIL ( SENDER, RECIPIENT, MESSAGE );

          COMMIT;

END;

PS: On Code4Coder.Com, we share best practices/codes snippets which are used in daily routine. Targeted technology areas are c#, Asp.net, VB.Net, Oracle, PL/SQL