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:
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
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
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.