Over the years, Oracle has gradually made it easier to send email from within a stored procedure. Here’s a short example:
/* Requires Oracle Database 10g or later */ BEGIN UTL_MAIL.send( sender => 'firstname.lastname@example.org' ,recipients => 'email@example.com' ,subject => 'API for sending email' ,message => 'Dear Friend: This is not spam. It is a mail test. Mailfully Yours, Bill' ); END;
When you run this block, the database will attempt to send this message using whatever SMTP host the DBA has configured in the initialization file (see the discussion in the next section).
The header for UTL_MAIL.SEND is:
ccIN VARCHAR2 DEFAULT NULL,
bccIN VARCHAR2 DEFAULT NULL,
subjectIN VARCHAR2 DEFAULT NULL, message IN VARCHAR2 DEFAULT NULL,
mime_typeIN VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
priorityIN PLS_INTEGER DEFAULT 3);
Most of the parameters are self-explanatory. One non-obvious usage hint: if you want to use more than one recipient (or cc or bcc), separate the addresses with commas, like this:
recipients => 'firstname.lastname@example.org, email@example.com'
Okay, so that’s pretty good if you have a recent version of Oracle, but what if you only have access to earlier versions, or what if you just want a little more control? You can still use the UTL_SMTP package, which is a little more complicated but nevertheless workable. If you want to code at an even lower level, you can use UTL_TCP, an external procedure, or a Java stored ...