Fear No SQL
Reactor Part 1 is a simple recursion with limited paths, so I used CONNECT BY where the device (source) of each row is contained within the output (target) of the prior row. So, in the sample data, the line “bbb: ddd eee” has source “bbb” and targets “ddd eee”. But we can see “bbb” is…
Playground This one felt tricky at first with lots of words and seemingly numerous combinations of how to arrange the boxes, but the solutions turned out to be fairly straightforward. It wasn’t necessary to recognize any particular trick to solving them; but I will acknowledge that using languages that have native set-based syntax likely made…
Continuing in the path of previous two posts, the package below supports all of the xoshiro/xoroshiro algorithms returning 64-bit values. The numbers in the names refer to the size of the state array that each method operates on. Using each of them follows the same pattern: For example… SQL> BEGIN 2 xoshiro.set_mode(‘xoroshiro128++’); 3 xoshiro.set_state(xoshiro.seed_tab(12345, 67890));…
A long time ago I ported the Mersenne Twister pseudo-random number generator to pl/sql. That algorithm, and my port, are showing their age so I started looking at other generators. A newer approach is found in the xoshiro/xoroshiro family by David Blackman and Sebastiano Vigna found here. I’ve chosen xoshiro256** as one of their “all-purpose,…
I like Chess, I like Math, and I like PL/SQL – implementing a chess rating algorithm in pl/sql satisfies three itches in one. The Glicko system was invented by Dr. Mark Glickman in 1995 as a way of rating players and it, along with its successor Glicko-2, have been adopted in a variety of online…
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…