This topic explains how to use Invantive SQL (Structured Query Language) to interact with the API of the verifymail.io service to verify email addresses and domains to exclude temporary/one-time email addresses, domains without MX record and proxy email domains. A check helps ensure that an email address can be traced to a physical person in environments with high integrity requirements.
Verifymail.io does not assess whether an email domain is for business or consumer use. For this purpose, https://www.ipqualityscore.com might be more suitable (5000 credits monthly in Free Plan, paid plans start at USD 499 monthly).
Verifymail.io API provides a simple API using key-based authentication. Documentation on the API can be found on https://verifymail.io/api-documentation. A free plan is available for at most 3 calls per day (paid plans start at USD 25 monthly).
Basis for the explanation is an Excel spreadsheet ‘domains.xlsx’ with a sheet ‘Sheet1’ with the following contents:
| A | |
|---|---|
| 1 | Domain |
| 2 | nu.nl |
| 3 | nos.nl |
| 4 | facebook.com |
| 5 | … etc … |
The following query first retrieves the contents of the Excel-sheet starting at row 2, and the queries the verifymail.io API service for each domain, parses the JSON and stores the results as table DomainScan@InMemoryStorage:
create or replace table DomainScan@InMemoryStorage
as
select jte.*
from exceltable
( worksheet 'Sheet1'
passing file 'c:\PATH\domains.xlsx'
skip first 1 rows
columns domein varchar2 position next
) ete
join httpdownload@DataDictionary
( url => 'https://verifymail.io/api/' || ete.domein || '?key=SECRETAPIKEY'
, diskCache => true
, diskCacheMaxAgeSec => 86400 * 7
, ignoreWebError => true
) htp
on htp.http_status_code = 200
join jsontable
( ''
passing htp.contents_char
columns block boolean path 'block'
, disposable boolean path 'disposable'
, deliverable_email boolean path 'deliverable_email'
, catch_all boolean path 'catch_all'
, privacy boolean path 'privacy'
, domain varchar2 path 'domain'
, email_address varchar2 path 'email_address'
, related_domains varchar2 path 'related_domains'
, mx_priority varchar2 path 'mx_priority'
, mx_ip varchar2 path 'mx_ip'
, mx_host varchar2 path 'mx_host'
, mx_fallback boolean path 'mx_fallback'
, mx boolean path 'mx'
, email_provider varchar2 path 'email_provider'
) jte
The results can be queried using:
select *
from DomainScan@InMemoryStorage
with results:
Please note that verifymail.io allows a limited number of API calls per month, depending on the plan. For performance and cost reasons, it is wise to cache the outcome of the verifymail.io API-calls depending on your use case.