Skip to content

API Reference

Jump to bottom
Jeffrey Kemp edited this page Aug 6, 2016 · 65 revisions

INDEX

Email address validation

Sending emails

Email queue/job management

Query Mailgun

Mailgun tags

Other

FUNCTION email_is_valid

Validate an email address (function wrapper). Parameters are:

  • p_address - (required) email address to validate

Returns true if address appears to be valid.

Requires: Mailgun Public API Key, Mailgun API URL

begin
  if mailgun_pkg.email_is_valid('chunkylover53@aol.com') then
    dbms_output.put_line('email is valid');
  else
    dbms_output.put_line('email is invalid');
  end if;
end;

Expected output:

email is valid

PROCEDURE validate_email

Validate an email address (procedure version). Parameters are:

  • p_address - (required) email address to validate
  • p_is_valid - (OUT) true if the email address appears to be valid
  • p_suggestion - (OUT) suggested correction for email address (may or may not be provided, regardless of whether is_valid is true or false)

Requires: Mailgun Public API Key, Mailgun API URL

declare
  is_valid   boolean;
  suggestion varchar2(512);
begin
  mailgun_pkg.validate_email
    (p_address    => 'chunkylover53@aol'
    ,p_is_valid   => is_valid
    ,p_suggestion => suggestion);
  if is_valid then
    dbms_output.put_line('email is valid');
  else
    dbms_output.put_line('email is invalid');
  end if;
  if suggestion is not null then
    dbms_output.put_line('suggested: ' || suggestion);
  end if;
end;

Expected output:

email is invalid
suggested: chunkylover53@aol.com

PROCEDURE send_email

Sends an email. To add any attachments, call the attach procedures before calling this. To add multiple recipients, call the send_to/send_cc/send_bcc procedures before calling it. Does not commit - for the email to be sent your program must issue a COMMIT. If your program issues a ROLLBACK instead, the email will not be sent.

To send an email immediately ("synchronously"), call mailgun_pkg.push_queue immediately after (push_queue does commit first).

Parameters are:

  • p_from_name - name of sender
  • p_from_email - (required) email address of sender
  • p_reply_to - email address for Reply To
  • p_to_name - name of recipient
  • p_to_email - (required if no recipients already added) email address of recipient(s); don't set this if you have set up recipients using the send_xx procedures
  • p_cc - email address(es) to CC (carbon copy) the email to
  • p_bcc - email address(es) to BCC (blind carbon copy) the email to
  • p_subject - (required) subject line
  • p_message - (required) message body - html allowed
  • p_tag - tag for this message (useful for topic-specific unsubscribe functionality)
  • p_mail_headers - json structure of tag/value pairs (see example below)
  • p_priority - priority for the queue; lower numbers are pulled from the queue first. Default is 3.

The recipient parameters (p_to_email, p_cc, p_bcc) can also accept comma-delimited lists of name <email>, e.g. Bob <bob@host.com>, Jane <jane@host.com>. In this case, you would not set p_to_name.

Requires: Mailgun Private API Key, Mailgun API URL, your Mailgun Domain

Send a simple email:

begin
  mailgun_pkg.send_email
    (p_from_email => 'Mr Sender <sender@example.com>'
    ,p_to_email   => 'Ms Recipient <recipient@example.com>'
    ,p_subject    => 'Test Subject'
    ,p_message    => 'Test Email Body'
    );
end;

Send an email using all the options, including adding an unsubscribe link:

begin
  mailgun_pkg.send_email
    (p_from_name  => 'Mr Sender'
    ,p_from_email => 'sender@example.com'
    ,p_reply_to   => 'reply@example.com'
    ,p_to_name    => 'Mr Recipient'
    ,p_to_email   => 'recipient@example.com'
    ,p_cc         => 'Mrs CC <cc@example.com>'
    ,p_bcc        => 'Ms BCC <bcc@example.com>'
    ,p_subject    => 'Test Subject'
    ,p_message    => '<html><body><strong>Test Email Body</strong>'
                  || '<p>'
                  || '<a href="' || mailgun_pkg.unsubscribe_link_tag || '">Unsubscribe</a>'
                  || '</body></html>'
    ,p_tag        => 'testtag2'
    ,p_mail_headers => '{ "Importance" : "high"' -- high/normal/low
                    || ', "Priority" : "urgent"' -- normal/urgent/non-urgent
                    || ', "Sensitivity" : "confidential"' -- personal/private/confidential
                    || ', "Expires" : "' || to_char(systimestamp + interval '7' day
                                                   ,mailgun_pkg.datetime_format)
                                         || '"' -- expiry date/time
                    || '}'
    ,p_priority     => 1
    );
end;

Refer to the send_xx and attach procedures (below) for more samples.

PROCEDURE send_to

Add a recipient to the "To" list. Call this BEFORE send_email. Parameters:

  • p_email - (required) email address or email spec (e.g. Bob Jones <bob.jones@example.com>)
  • p_name - Full name of recipient (if not set, it will use First + Last name if provided)
  • p_first_name - First / given name
  • p_last_name - Last / surname
  • p_id - ID or any other reference useful to your system
  • p_send_by - (default is "to") set to 'cc' or 'bcc' to override the send method (otherwise, call send_cc or send_bcc)
begin
  mailgun_pkg.send_to('Mr Recipient <recipient1@example.com>');

  mailgun_pkg.send_to
    (p_email      => 'bob.jones@example.com'
    ,p_first_name => 'Bob'
    ,p_last_name  => 'Jones'
    ,p_id         => 'id123');

  mailgun_pkg.send_to
    (p_email   => 'jane.doe@example.com'
    ,p_name    => 'Jane Doe'
    ,p_send_by => 'cc');

  mailgun_pkg.send_email
    (p_from_email => 'Mr Sender <sender@example.com>'
    ,p_subject    => 'test subject'
    ,p_message    => 'Hi %recipient.first_name%,'
                  || '<p>'
                  || 'This is the email body.'
                  || '<p>'
                  || 'This email was sent to %recipient.name%.'
                  || '<br>'
                  || 'Reference: %recipient.id%'
                  || '<p><a href="%unsubscribe_url%">Unsubscribe</a>'
    );

  mailgun_pkg.push_queue;

  commit;

exception
  when others then
    mailgun_pkg.reset; -- clear any recipients from memory
    raise;
end;

PROCEDURE send_cc

Add a recipient to the "CC" (Carbon Copy) list. Call this BEFORE send_email. Parameters:

  • p_email - (required) email address or email spec (e.g. Bob Jones <bob.jones@example.com>)
  • p_name - Full name of recipient (if not set, it will use First + Last name if provided)
  • p_first_name - First / given name
  • p_last_name - Last / surname
  • p_id - ID or any other reference useful to your system
begin
  mailgun_pkg.send_cc('The Manager <manager@example.com>');
  mailgun_pkg.send_email(...);
end;

PROCEDURE send_bcc

Add a recipient to the "BCC" (Blind Carbon Copy) list. Call this BEFORE send_email. Parameters:

  • p_email - (required) email address or email spec (e.g. Bob Jones <bob.jones@example.com>)
  • p_name - Full name of recipient (if not set, it will use First + Last name if provided)
  • p_first_name - First / given name
  • p_last_name - Last / surname
  • p_id - ID or any other reference useful to your system
begin
  mailgun_pkg.send_bcc('The Boss <ceo@example.com>');
  mailgun_pkg.send_email(...);
end;

PROCEDURE attach

Call this BEFORE send_email to add an attachment or inline image. Multiple attachments may be added. If inline is true, you can include the image in the email message by:

<img src="cid:myfilename.jpg">
  • p_file_content - (required) CLOB or BLOB data (overloaded)
  • p_file_name - (required) File name
  • p_content_type - (required) MIME content type, e.g. image/png
  • p_inline - (default is FALSE) - set to TRUE to send an inline image
-- send an email with some attachments
declare
  clob_content clob;
  blob_content blob;
begin

  -- generate a largish text file
  dbms_lob.createtemporary(clob_content,false);
  clob_content := lpad('x', 32767, 'x');
  dbms_lob.writeappend(clob_content, 32767, lpad('y',32767,'y'));
  dbms_lob.writeappend(clob_content, 3, 'EOF');
  dbms_output.put_line('file size=' || dbms_lob.getlength(clob_content));

  -- load a binary file
  -- source: https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/file_util_pkg.pkb
  blob_content := alex.file_util_pkg.get_blob_from_file
    (p_directory_name => 'MY_DIRECTORY'
    ,p_file_name      => 'myimage.jpg');

  mailgun_pkg.attach
    (p_file_content => 'this is my file contents'
    ,p_file_name    => 'myfilesmall.txt'
    ,p_content_type => 'text/plain');

  mailgun_pkg.attach
    (p_file_content => clob_content
    ,p_file_name    => 'myfilelarge.txt'
    ,p_content_type => 'text/plain');

  mailgun_pkg.attach
    (p_file_content => blob_content
    ,p_file_name    => 'myimage.jpg'
    ,p_content_type => 'image/jpg'
    ,p_inline       => true);

  mailgun_pkg.send_email
    (p_from_email => 'Mr Sender <sender@example.com>'
    ,p_to_email   => 'Mrs Recipient <recipient@example.com>'
    ,p_subject    => 'test subject ' || to_char(systimestamp,'DD/MM/YYYY HH24:MI:SS.FF')
    ,p_message    => '<html><body><strong>Test Email Body</strong>'
                  || '<p>'
                  || 'There should be 2 attachments and an image below.'
                  || '<p>'
                  || '<img src="cid:myimage.jpg">'
                  || '</body></html>'
    );

  mailgun_pkg.push_queue;

  commit;

exception
  when others then
    mailgun_pkg.reset; -- clear any attachments from memory
    raise;
end;

PROCEDURE reset

Call this to clear any recipients and attachments (note: send_email does this for you) e.g. if your proc raises an exception before it can send the email. No parameters.

begin
  ...mailgun_pkg. etc...
exception
  when others then
    mailgun_pkg.reset;
    raise;
end;

PROCEDURE create_queue

Create the queue and related queue table for asynchronous emails. The queue is a required component for the API. The procedure has the following optional parameters:

  • p_max_retries - (default is 10) allow this many failures before giving up on a message
  • p_retry_delay - (default is 10) wait this many seconds before trying a failed message again
exec mailgun_pkg.create_queue;

PROCEDURE drop_queue

Drop the queue and related queue table.

exec mailgun_pkg.drop_queue;

PROCEDURE purge_queue

Purge any expired (failed) emails stuck in the queue. It takes the following optional parameter:

  • p_msg_state (default is EXPIRED) - msg_state to purge; NULL means ALL messages in the queue get dropped
exec mailgun_pkg.purge_queue;

PROCEDURE push_queue

Send all emails in the queue. This can be done synchronously or asynchronously. This pulls any emails from the queue in order of priority, enqueue time, and calls the Mailgun API for each.

If the call to Mailgun fails on any particular email, it will stop further processing (emails sent up to that point will be successful). The failed email will be delayed in the queue for at least 10 seconds, and retried a maximum of 60 times before being dumped in the exception queue.

  • p_asynchronous (default is TRUE)

If p_asynchronous = TRUE (i.e. asynchronous), no COMMIT is issued and the queue will be processed in a separate session (via DBMS_JOB) after the session has issued a COMMIT. If the session issues a ROLLBACK, the queue will not be processed. This is the recommended setting for calling push_queue from an application process.

If p_asynchronous = FALSE (i.e. synchronous), the session will be COMMITted first and the queue processed in the calling session. The calling process will wait until the queue has been processed. This is the recommended setting for calling push_queue from a job set up expressly for this purpose.

exec mailgun_pkg.push_queue(p_asynchronous => false);

begin
  mailgun_pkg.send(...);
  mailgun_pkg.push_queue;
end;

PROCEDURE create_job

Create a scheduler job to periodically call push_queue. It takes one parameter:

  • p_repeat_interval - Override the default frequency for the job. The default frequency is every 5 minutes.

NOTE: if you decide to not create a job to periodically call push_queue, you must call it in your code every time you send an email.

begin
  mailgun_pkg.create_job
    (p_repeat_interval => 'FREQ=MINUTELY;INTERVAL=10;');
end;

PROCEDURE drop_job

Drop the scheduler job which calls push_queue.

exec mailgun_pkg.drop_job;

FUNCTION get_stats

Get email statistics.

  • p_event_types - default is "all"; comma-delimited list of event types ("accepted", "delivered", "failed", "opened", "clicked", "unsubscribed", "complained", "stored")
  • p_resolution - default is "day"; can be "hour", "day" or "month"
  • p_start_time - default is 7 days prior to end time
  • p_end_time - default is now
  • p_duration - time counting backwards from p_end_time in units of p_resolution, e.g. 7 days

Returns an array of t_mailgun_stat which comprises the following attributes:

  • stat_datetime - date/time
  • resolution - hour, day or month
  • stat_name - e.g. "accepted" (refer to full list, below)
  • stat_detail - e.g. "total"
  • val - the number of email requests for this category

Requires: Mailgun Private API Key, Mailgun API URL, your Mailgun Domain

Get all stats for the last 7 days, by day:

select * from table(mailgun_pkg.get_stats);

Get all delivered for the last 24 hours, by hour:

select * from table(mailgun_pkg.get_stats
  (p_event_types => 'delivered'
  ,p_resolution  => 'hour'
  ,p_duration    => 24));

Get all failed in the prior two months, by month:

select * from table(mailgun_pkg.get_stats
  (p_event_types => 'failed'
  ,p_resolution  => 'month'
  ,p_start_time  => add_months(trunc(sysdate,'MM'), -2)
  ,p_end_time    => trunc(sysdate,'MM') - 0.00001
  ));

The following statistics can be reported:

  • accepted - incoming, outgoing, total
  • delivered - smtp, http, total
  • failed.temporary - espblock
  • failed.permanent - suppress-bounce, suppress-unsubscribe, suppress-complaint, bounce, total
  • stored - total
  • opened - total
  • clicked - total
  • unsubscribed - total
  • complained - total

NOTE: there are some limits that may result in the error 400 Bad Request, e.g. asking for hourly resolution for more than a month.

Refer to https://documentation.mailgun.com/api-stats.html for more information on retention periods and for explanations of the various statistics.

FUNCTION get_tag_stats

Get email statistics for a tag.

  • p_tag - (required) tag name - cannot contain spaces
  • p_event_types - default is "all"; comma-delimited list of event types ("accepted", "delivered", "failed", "opened", "clicked", "unsubscribed", "complained", "stored")
  • p_resolution - default is "day"; can be "hour", "day" or "month"
  • p_start_time - default is 7 days prior to end time
  • p_end_time - default is now
  • p_duration - time counting backwards from p_end_time in units of p_resolution, e.g. 7 days

Returns an array of t_mailgun_stat which comprises the following attributes:

  • stat_datetime - date/time
  • resolution - hour, day or month
  • stat_name - e.g. "accepted" (refer to full list, below)
  • stat_detail - e.g. "total"
  • val - the number of email requests for this category

Requires: Mailgun Private API Key, Mailgun API URL, your Mailgun Domain

Get all stats for a tag for the last 7 days, by day:

select * from table(mailgun_pkg.get_tag_stats(p_tag => 'mytag'));

Refer to get_stats for more info about what is returned by this function.

FUNCTION get_events

Planned for release 0.6

Get a log of email events from mailgun for your account.

  • p_start_time - default is now; query starting from this date/time
  • p_end_time - stop returning events at this date/time; if this is a date/time prior to p_start_time, the events will be returned in reverse order. Default is to go back in history as far as possible.
  • p_page_size - rows to fetch per API call; default 20; max 300
  • p_event - filter expression on event type (accepted,rejected,delivered,failed,opened,clicked,unsubscribed,complained,stored)
  • p_sender - filter expression, e.g. '"sample@example.com"'
  • p_recipient - filter expression, e.g. 'gmail OR hotmail'
  • p_subject - filter expression, e.g. 'foo AND bar'
  • p_tags - filter expression, e.g. 'NOT internal'
  • p_severity - for failed events: 'temporary' or 'permanent'

Returns a pipelined array of t_mailgun_event:

  • event - event type
  • event_ts - date/time
  • event_id - unique id within the day
  • message_id
  • sender
  • recipient
  • subject
  • attachments - comma-delimited list of file names
  • size_bytes - total size in bytes of message and attachments
  • method - e.g. smtp or http
  • tags - comma-delimited list of tags
  • user_variables - comma-delimited list of name/value pairs
  • log_level - e.g. error, info
  • failed_severity - e.g. temporary, permanent
  • failed_reason
  • delivery_status
  • geolocation - for opened/clicked events
  • recipient_ip - for opened/clicked events
  • client_info - for opened/clicked events
  • client_user_agent - for opened/clicked events

Requires: Mailgun Private API Key, Mailgun API URL, your Mailgun Domain

Get recent events log:

select * from table(mailgun_pkg.get_events);

Get failed emails, 50 records per API call:

select * from table(mailgun_pkg.get_events
  (p_page_size => 50
  ,p_event     => 'failed'));

Get emails successfully sent to anyone with a gmail or hotmail address on a particular day:

select * from table(mailgun_pkg.get_events
  (p_event      => 'delivered'
  ,p_start_time => date'2016-08-05'
  ,p_end_time   => date'2016-08-06' - 0.00001
  ,p_recipient  => 'gmail OR hotmail'
  ));

FUNCTION get_tags

Planned for release 0.6

Get a list of tags.

  • p_limit - maximum number of records to return (default 100)

Returns a pipelined array of t_mailgun_tag:

  • tag_name
  • description
select * from table(mailgun_pkg.get_tags);

PROCEDURE update_tag

Planned for release 0.6

Add or update a tag.

  • p_tag_name - (required) name of the tag (cannot contain spaces)
  • p_description - (optional) description for the tag
begin
  mailgun_pkg.update_tag
    (p_tag_name    => 'mytag'
    ,p_description => 'my tag description');
end;

PROCEDURE delete_tag

Planned for release 0.6

Delete a tag.

  • p_tag_name - (required) name of the tag (cannot contain spaces)
begin
  mailgun_pkg.delete_tag (p_tag_name => 'badtag');
end;

PROCEDURE verbose

Set verbose option on/off; if set on, all data sent to/from mailgun server will be written to the debug log.

Turn verbose mode on:

mailgun_pkg.verbose;

Turn verbose mode off:

mailgun_pkg.verbose(false);

Constants

You may use the following constants to insert these substitution strings in your email subject or message. These are interpreted by Mailgun.

mailgun_pkg.unsubscribe_link_all = %unsubscribe_url%
mailgun_pkg.unsubscribe_link_tag = %tag_unsubscribe_url%
mailgun_pkg.recipient_name       = %recipient.name%
mailgun_pkg.recipient_first_name = %recipient.first_name%
mailgun_pkg.recipient_last_name  = %recipient.last_name%
mailgun_pkg.recipient_id         = %recipient.id%
mailgun_pkg.recipient_email      = %recipient.email%

You may use the following constant when sending a date as a formatted string to be interpreted by mailgun's API:

mailgun_pkg.datetime_format      = Dy, dd Mon yyyy hh24:mi:ss tzh:tzm

You may use the following constant if you don't have access to UTL_TCP.CRLF:

mailgun_pkg.crlf                 = CRLF

Clone this wiki locally