jeffreykemp / mailgun-plsql-api Goto Github PK

get_complaints

Get a list of complaints - recipients who marked your messages as spam.

• p_limit - max records to return (default 100)

Returns an array of records:

• email_address
• created_dt

https://documentation.mailgun.com/api-suppressions.html#view-all-complaints

update_tag

Update the description for a tag.

Parameters:

• p_tag - tag to update
• p_description - new description

https://documentation.mailgun.com/api-tags.html#tags

enc_chars fails with ORA-06502: PL/SQL: numeric or value error: character string buffer too small if the any 8000-character chunk of the message body, when encoded, happens to require more than 8000 characters to be stored.

add_unsubscribe

Add an address to the unsubscribe list.

• p_email_address
• p_tag (default is * for all emails)

https://documentation.mailgun.com/api-suppressions.html#unsubscribes

Method to add many recipients. Each recipient has an email address, name, to/cc/bcc, and ID. Subject and message can have substitution strings.

e.g. for inline CSS file

Hi Jeffrey Kemp, This is great work without no doubt.

I have an issue, After sending an email to the client, then client reply to sender then how can I get this email or where I can check?

If there are no email receive option. plz add this feature for next release.

The package logs all data sent to and received from the mailgun server; add a "verbose" option and only log this data if verbose is on. Default to off.

Calls to send_email should put the email into a queue.

Create a separate optional package mailgun_aq_pkg which adds an AQ layer over the top of mailgun_pkg.send_email.

Add p_priority which allows emails to get bumped to the head of the queue (or pushed to the back of the queue).

Add push_queue procedure which can be called periodically and/or immediately after requesting an email.

Add a log table to log emails and their results.

Add schema-level types for emails and related data.

Include procedures to manage the queue and the scheduler job.

Currently, special characters such as MS Word "smart quotes" are shown as ? in the email. Encode these so they can be sent.

get_events

Use the Mailgun Events API to retrieve a log of email events.

• p_start_time
• p_end_time
• p_page_size - default 20 (max 300)
• p_event - default null (filter expression)
• p_sender - default null (filter expression)
• p_recipient - default null (filter expression)
• p_subject - default null (filter expression)
• p_size - default null (filter expression) - couldn't get this working (consistent "400 Bad Request")
• p_tags - default null (filter expression)
• p_severity - default null (for failed events - "temporary" or "permanent")

Filter Expressions: refer to mailgun documentation for examples

Returns an array of records:

• event (accepted, rejected, delivered, failed, opened, clicked, unsubscribed, complained, stored)
• event_ts (timestamp)
• event_id (unique id within a day)
• message_id
• sender
• recipient
• subject
• attachments (comma-delimited list of filenames)
• size_bytes
• method (e.g. smtp)
• tags (comma-delimited list of tags)
• user_variables
• delivery_status (e.g. error message)
• delivery_status_code (e.g. error code)
  For opened/clicked/unsubscribed/complained:
• geolocation (country, region, city)
• recipient_ip
• client_info (type, os, device, name)
• client_user_agent

https://documentation.mailgun.com/api-events.html#events

Configurable setting to restrict maximum size (including attachments) to 25MB.

• init: add p_log_retention_days - default 30 days
• procedure purge_logs: delete mailgun_email_log where requested_ts more than p_log_retention_days ago
• procedure create_purge_job (p_repeat_interval) - default weekly on Sundays at 12am
• procedure drop_purge_job

delete_unsubscribe

Removes an address from the unsubscribed list.

• p_email_address
• p_tag (optional - if null, completely removes the address from the list)

https://documentation.mailgun.com/api-suppressions.html#unsubscribes

get_tags

Get a list of tags.

Parameters:

• p_limit (default 100) - max entries to retrieve

Returns an array of records with:

• tag_name
• description

Refer https://documentation.mailgun.com/api-tags.html#tags

Implement a whitelist mechanism - e.g. validate email recipient domain against a whitelist of domains.

When a recipient fails the whitelist, allow one of the following to happen:

• silently drop the email;
• raise an exception;
• change the domain to a preferred domain; OR
• change the recipient to a single address.

Also, allow a separate whitelist for the sending email domain. e.g. "if sender is not [email protected], change the sender email address to [email protected]".

• init: add p_default_sender_name, p_default_sender_email
• send_mail: if p_from_email is null, use p_default_sender_email; if p_from_name is null as well, use p_default_sender_name (but don't use p_default_sender_name if p_default_sender_email is set)

Only applies to Logger version

If a recipient name or other attribute includes an ampersand (&) it gets encoded as \u0026 before sending as JSON to mailgun. Mailgun does not decode this when performing the substitution in the email body text for bulk mailing.

Store in the settings table

Hi Jeff,

We are facing issues in sending mail through API. I think this issue is related to either ACL or the certificate as all the other values are provided by Mailgun.

Can you please guide me in resolving the issue?

When pushing the queue we are encountering the following issue:

ORA-20000: post failed 301 Moved Permanently [app.optimium.in/messages]

Whle using mailgun_pkg.email_is_valid we are encountering the following error:

ORA-29273: HTTP request failed
ORA-12545: Connect failed because target host or object does not exist
ORA-06512: at "SYS.UTL_HTTP", line 380

Thanks and regards

Sammeer

• settings table to have separate settings for each domain
• some settings are global (prod name; oracle wallet)
• one domain is the "default" domain
• optional p_domain parameter to send_email and other methods where applicable
• add domain to mailgun_email_logs
• add domain to t_mailgun_email
• API calls to get list of domains from mailgun
• API calls to add/update mailgun domains

get_tag_stats

Get statistics for a given tag.

Parameters:

• p_tag
• p_event (all, accepted, delivered, failed, opened, clicked, unsubscribed, complained, stored)
• p_start_time - default is 7 days ago
• p_end_time - default is now
• p_resolution - default is day (hour, day, month)
• p_duration

https://documentation.mailgun.com/api-tags.html#tags

• event types (default is "all") comma-delimited list of accepted, delivered, etc. (refer to mailgun doc for complete list of event types)
• resolution (hour/day/month, default is day)
• start time (default is 7 days prior to end time)
• end time (default is now)
• duration (hours/days/months, as per resolution)

Returns a table of records with structure:

• time
• resolution (hour/day/month)
• stat_name (e.g. "accepted total", "failed permanent")
• stat_detail (e.g. "suppress-bounce", "espblock")
• value (integer)

It should be noted that some stats will overlap (e.g. "accepted total" = "accepted outgoing" + "accepted incoming").

https://documentation.mailgun.com/api-stats.html#stats

Note that stats have different retention policies depending on resolution (hourly/daily/monthly).

Allow sending a test email, optionally using a given set of mailgun parameters (i.e. doesn't change the mailgun settings).
Bypass AQ - make the connection in the current session.

If the push_queue process gets an error from the mailgun server, it just logs the failure using the default logging procedures.

Instead, it should log the failure in the mailgun_email_log table.

On running mailgun_pkg.email_is_valid I am encountering the following error:

Error report -
ORA-20000: mailgun setting not set "pubkey-xxxxxxxxxxxxxxxxxxxxxxx" - please setup using MAILGUN_PKG.init()

Additionally,

on executing mailgun_pkg.push_queue I encountered the following error:
ORA-20000: mailgun setting not set "app.optimium.in" - please setup using MAILGUN_PKG.init()

I have noted down the API keys from mailgun.

get_bounces

Get a list of recipient mailbox suppressions due to Bounces.

• p_limit - max records to return (default 100, max 10000)

Returns array of records:

• email_address
• bounce_code
• bounce_error
• created_dt

https://documentation.mailgun.com/api-suppressions.html#bounces

regression from v0.2

get_unsubscribes

Get a list of unsubscribed email addresses.

• p_limit - max records to return (default 100, max 10,000)

Returns an array of records:

• email_address
• tag ("*" if unsubscribed from all)
• created_dt

https://documentation.mailgun.com/api-suppressions.html#unsubscribes

Allow adding arbitrary mail headers, such as:

Importance (high, normal, low)
Priority (normal, urgent, non-urgent)
Sensitivity (personal, private, confidential)
Expires (date/time, e.g. Thu, 28 Apr 2016 22:38:47 +08:00)

e.g.

{
  "Importance" : "high",
  "Priority" : "urgent",
  "Sensitivity" : "confidential",
  "Expires" : "Thu, 28 Apr 2016 22:38:47 +08:00"
}

Note: not to be confused with the priority parameter in the AQ version.

dbms_lob.getlength returns size in characters, not bytes.

delete_complaint

Remove an email address from the Complaints list.

• p_email_address

https://documentation.mailgun.com/api-suppressions.html#view-all-complaints

delete_bounce

Clear a given bounce event (allow delivery to the email address until it bounces again).

• p_email_address

delete_bounces_all

Clear all bounced email addresses (no parameters).

https://documentation.mailgun.com/api-suppressions.html#bounces

Allow client programs to initiate a push_queue asynchronously (so that the push won't happen unless they commit, and the push won't occur in their session).

delete_tag

Delete a tag.

Parameters:

• p_tag_name

https://documentation.mailgun.com/api-tags.html#tags

Implement an email template system.

• Store a template with name, subject, body, substitution variables, attachments (images, css)
  • sub-templates for Region(s), Sidebar(s), Header and Footer
• API to create/update/delete templates
• send_email accepts template name as parameter, plus substitution variables

All templates (including sub-templates) support these substitution variables:

• #GLOBAL_NAME# = database global name
• #DT:format# = date/time (specify Oracle format)
• #SENDER:NAME#
• #SENDER:EMAIL#
• #variable# = any arbitrary template variable

The main template supports these substitution variables:

• #HEADER#
• #BODY#
• #REGION1# .. #REGION9#
• #SIDEBAR1# .. #SIDEBAR9#
• #FOOTER#

Each Region and Sidebar sub-template supports these substitution variables:

• #TITLE#
• #CONTENT#
• #IMAGE#

Note: Mailgun substitution variables also allowed, e.g. %recipient.email%, %recipient.name%, %tag_unsubscribe_url%

Allow quarantining emails from a non-prod (dev or test) database instance. e.g. suppress entirely, or send them to a designated recipient list.

• init - add p_prod_instance_name, p_non_prod_recipient
• send_email - if p_prod_instance_name is set, compare to global_name;
  • if it matches, send the email as normal
  • if it doesn't match, treat this instance as a dev/test instance:
    • if p_non_prod_recipient is set, replace the recipient list with just p_non_prod_recipient (list allowed);
    • otherwise, don't send the email.