Let's assume for a moment that a table-API or maybe a trigger are used to validate DML operations on the HR.EMPLOYEES table. One of the table's columns stores email addresses, and you'd like to ensure that the data within that column represents a valid email address. There are many ways to ensure the validity of email addresses, for example using SQL Domains.
Please refer to validator's GitHub project site for more details about the project's license and implications of use. The article assumes your legal and IT Security departments (as well as any other party) agreed that it's safe to use the module in your code. Using 3rd party code in your application typically requires certain compliance steps to be completed which are out of scope of this article.
The Validator module can be downloaded from multiple sources, as long as you pick a trustworthy one it doesn't really matter where the file originates from. You need to ensure that you get the ECMA Script (ESM) version of the module from your preferred CDN as they are the only ones supported in Oracle. The following example demonstrates how to get the module's ECMA Script version from JSDeliver, there are of course other options as well:
curl -Lo validator.min.js https://cdn.jsdelivr.net/npm/validator@latest/+esm
This will generate the source file to be used later. If you are after a specific version of the module substitute latest with the release number you need.
With the file in place it is possible to create the module:
The database will tell you that the module has been created successfully. You can see for yourself:
create or replace function isEmail( p_str varchar2 ) return boolean as mle module validator signature 'default.isEmail(string)'; /
SQL> select isEmail('abc'); ISEMAIL('AB ----------- FALSE SQL> select isEmail('email@example.com'); ISEMAIL('AB ----------- TRUE
Use the following snippet if you are unsure how to create a developer account on your Oracle 23c Free - Developer Release database. Note that the code will prompt you for a password.
Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.