Fear No SQL
I found this functional surprise while responding to questions about my previous article. When you invoke the GET_KEYS method of JSON_OBJECT_T, if the collection is empty, 19c will return a NULL value; 21c will return a JSON_KEY_LIST collection of 0 elements. 19c SQL> DECLARE 2 v_version VARCHAR2(20); 3 v_compatible VARCHAR2(20); 4 v_keylist json_key_list; 5 BEGIN…
When presented with a JSON document you may need to process all of its contents. You’ll need to read each value and if there are any objects or arrays within the document you’ll need to read through the contents of each of them as well. Fortunately, the JSON structure is, intentionally, simple in its construction.…
Sending Email with Oracle If using an older version of the Oracle database (11gR1 or earlier) the UTL_SMTP package does not have authentication APIs built in. You may also run into a situation where a server implements a mechanism that UTL_SMTP does not support. You still have options though, by implementing some of the authentication…
Sending Email with Oracle UTL_SMTP UTL_SMTP has a richer functionality than the UTL_MAIL or APEX_MAIL packages; but that extra versatility comes with more complicated syntax. Before digging into the details of how the package works I’ll first show a short UTL_SMTP example, simply sending “Hello World!” to two recipients. DECLARE crlf CONSTANT VARCHAR2(2) := UTL_TCP.crlf;…
Sending Email with Oracle APEX_MAIL The APEX_MAIL package is a more robust version of what you’ll find with UTL_MAIL, but it is only available when APEX is installed. It also requires pre-configuration of the smtp server, port, authentication, wallets, and encryption. These features require APEX-administrative privileges to set. Since APEX might not be installed and…
Sending Email with Oracle Surprisingly, the original SMTP protocol provided no means of authenticating; but with the rise of spam engines and malicious users it quickly became a necessary component and was later added with addendum RFC’s. Despite authentication being part of the standard at the time, when Oracle introduced the UTL_SMTP package in version…
Sending Email with Oracle UTL_MAIL The UTL_MAIL package is an interesting addition to the database functionality because it came after UTL_SMTP and could have been added to the existing functionality, but was instead split off into its own package. The package is not installed by default either. If you wish to use UTL_MAIL, your DBA…
I’ve found myself needing to search LDAP data more often and each time I do it’s been sort of obnoxious to have to rewrite variations of mostly similar DBMS_LDAP routines. Frequently I then need to use that data in a SQL query of some sort to combine it with other data found in one or…