Fear No SQL
21c introduced several new iterator syntax. I’ll explore each of them in a series of articles beginning with the (in my opinion) most interesting of them, the PAIRS OF iterator. If you’ve used PL/SQL for a while you’ve probably created simple FOR loops iterating over some set of values, for example: 1 to 10. BEGIN…
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…
Beginning with Oracle Database version 9.0.1, the ALTER SYSTEM command allowed you to add comments to your parameter changes. These comments can be up to 255 characters long. This feature came with the introduction of the spfile. In earlier versions, all parameter changes were made in text files, where you could embed comments with #…
After completing my article about skewness of a sample for Joel Kallman Day I decided I could do one more and write up the related functionality for skewness of a population. Like skewness of a sample, the purpose of the function is to indicate the degree and direction of asymmetry in a data distribution. Like…
Several years ago I needed to calculate the skewness of some data. That is, an indication of how asymmetric points are within a distribution. Unfortunately, I found there was no native feature to do this within SQL. So I wrote my own with the Oracle Data Cartridge Interface (ODCI.) Using ODCI you can create your…
For all the great new functionality introduced in a new database release, sometimes it’s the simple and small things that generate the most buzz. The 23ai introduction of Boolean data types in SQL may be one of the biggest. For many years in prior releases the argument against a Boolean SQL type is that it…
If you have a column of values, Oracle provides functions to find each of these values: AVG() for the mean (average), MEDIAN() for the median, and STATS_MODE for the mode. STATS_MODE came in 10g. Before that the mode is also possible but requires extra steps for counting and sorting. For example, given the values 1,…
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…
One of the great features of Oracle is the ability to create a table function. That is, a function returning a collection type that can be used as the source for a query. These functions then allow a developer to provide a simple SQL api to query rows and columns from a variety of data…
What is ASH? AWR and Activity Types Average Active Sessions Graphical Visualizations (this page) Up to this point in my ASH series all of the output was simply rows and columns of data, sometimes augmented with an ascii, text based chart. This time I’ll explore a few different tools to construct better visualizations. One of…
While testing some date/time functions today I came across an interesting quirk in Oracle’s Gregorian calendar and different date representations. In the Gregorian calendar, the day after October 4, 1582 is October 15, 1582 (not all countries adopted the calendar immediately, so they have different gaps, but Oracle observes the calendar as it was originally…