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…
23ai introduces dangling predicates as a new feature for CASE statements. Per the Oracle documentation they extend the simple case structure, allowing you to match WHEN clauses for conditions other than single values. That is, prior to 23ai, you could write a CASE statement like this: CASE p_value WHEN 1 then ‘White’ WHEN 2 then…
When using the new SELECT AI syntax of 23ai, you can specify the model you’d like to use with OpenAI’s ChatGPT service. Before using the functionality, you must first create an ACE (access control entry) for the OpenAI host with your user as the principal and grant execute to the cloud packages DBMS_CLOUD and DBMS_CLOUD_AI.…
In two prior articles I used pl/sql and java to build functions that would return the keys of a JSON document as a collection which could be queried as a table. 23c includes a new MLE (multilingual engine) functionality providing a mechanism to define a stored procedure using JavaScript. This seemed like an ideal solution…
In my previous article I provided a few PL/SQL options for returning a collection of keys from a JSON document. They are simple to use and work well enough for most JSON sources I’ve encountered. SQL> select * from json_keys(json(‘{“a”:”test 1″, “b”: “test 2”, “c” : “test 3”}’)); COLUMN_VALUE ——————————————————————————– a b c Iudith Mentzel…
Oracle supports a wide variety of JSON parsing, querying, and construction functionality; but, does not provide a means in SQL of listing the keys of JSON object (at least not as of 23ai). While this is not a common need, it is a feature I have wanted a few times. Fortunately, it is an easy…
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 #…
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.…
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 amazing and grand new features coming in 23ai (formerly 23c) for the database… sometimes it’s the little things that are really appreciated. SQL*Plus in 23ai has a new PING command that mostly takes the place of TNSPING. This is fantastic news for all the fans of the Instant client that never included…
In my previous article I showed how to build table functions, including pipelined functions with PL/SQL. To provide a simple, minimal example I created a function that would generate a list of numbers from 1 to some value provided by the invoker. CREATE OR REPLACE FUNCTION nt_function_pipelined(n IN INTEGER) RETURN numtab PIPELINED IS BEGIN FOR…
In a previous article I looked at creating and using collections within SQL. I mentioned, but did not explore functions returning Nested Table and Varying Array collection types in that article. Those fuctions, known as table functions, will be the subject here. A table function can return either of the two collection types in their…
When you invoke a java stored procedure you will instantiate the internal JVM (Java Virtual Machine) to execute that code. Since 11gR1, you can clear your session state with functions in the DBMS_JAVA package. DBMS_JAVA.endsession – this will clear your Java session state. DBMS_JAVA.endsession_and_related_state – this will clear your Java session state. It will also…
A few years ago I wrote a couple articles about Day to Second and Year to Month intervals. In those articles I included examples of user-defined aggregates allowing you to sum the corresponding interval values. 23ai (formerly known as 23c) though has, thankfully, made those functions obsolete. As a quick recap for comparison… select dsintervalsum(result)…
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…
A collection of conversions, formats, math, generators, and conditions for date and timestamp data types. I’ll show most examples as simple SQL statements from DUAL. Many of the expressions could be used directly in PL/SQL without a query. Unless otherwise specified my examples have NLS_DATE_LANGUAGE=American. If that is not the case for your sessions you…
A common task for scheduling is to find when certain dates might occur. The package below includes functions to return various holidays observed in the U.S. for various years. The dates can be returned individually by calling specific holiday functions and passing in the year. For example: SQL> select holidays.independence_day(2023) from dual; HOLIDAYS.I ———- 2023-07-04…
Next in my series of combinatoric/probability functions… let’s look at the Binomial Distribution. This distribution describes sequences of independent events that produce true/false results- such as flipping “heads” on a coin or drawing a particular card at random from from a deck and then shuffling it back into the deck before drawing the next card.…
Calculating factorials quickly explode into large results. The SQL and PL/SQL numeric types can’t hold the extremely large values generated by factorial computation of even relatively small numbers. Since several numeric types are subtypes, they inherit the limits of their parent type. All of the types will eventually overflow when the results get too big.…
Recently I was working on a SQL performance problem and looking at the statement I found an unusual clause of the form where NVL(date_column,sysdate) < date ‘2022-10-01′ Note: in the example I’ve used a date literal for the sake of illustration. The actual code used a variable; but the problem exists either way. Checking with…
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,…
When calculating common probabilities they will often include factorials which quickly explode. In a previous article I provided functions that help cancel some of the factorial expansion thus helping to keep the values within the limits of the NUMBER type. The Hypergeometric Distribution is a mouthful of a name, but the idea is fairly easy…
I’ve written about collections before; but usually in the context of solving some other problem rather than focusing on them specifically. I’ll begin with a quick review of some of those previous topics and then dig into the syntax details of working with collection types. Previous Examples Using the COLLECT operator we can create a…
I polled some friends for topics and one asked for an article on collections. I’ve written about collections before; but usually in the context of solving some other problem rather than focusing on them specifically. Before digging into the collection types and the SQL and PL/SQL syntax of using them; I want to start with…
Sending Email with Oracle In this series, parts 6 through 9 covered various MIME block and multipart types. Now we’ll put them all together. Taking all these steps may seem contrived but a structure such as this will likely be used by most modern email clients you may already be using. In the code samples…
Sending Email with Oracle All of the examples in prior articles assume the client will be able to render the content as we intended. Some email clients however, will not; either because the software does not support certain features, or the recipient has disabled them. It’s not feasible in the general case to construct and…
Sending Email with Oracle Up to this point in the series all emails have been simple, plain text with or without attachments. Often we’ll want to send emails with special formatting – different fonts, highlighting, tables, embedded images, etc. Much like SMTP not directly supporting attachments, it doesn’t support cosmetic features of formatted content either.…
Sending Email with Oracle At this point in the series you should be able to send simple, plain text email with UTL_SMTP. Often though, we’ll want to send emails with special formatting – different fonts, highlighting, tables, images, etc. We’ll want to send attachments too. It surprises many developers new to the UTL_SMTP package to…
Sending Email with Oracle UTL_MAIL and APEX_MAIL have been discussed previously and each include a subset of attachment functionality explained in their respective articles. For simple applications these may be sufficient. To go beyond the functionality of those packages; or if they aren’t available in your database you will need to use UTL_SMTP. As mentioned…
I needed to calculate some free fall timings recently and while I could have used a calculator I decided I’d write a package to encapsulate the math for more convenient reuse if I ever needed to do so again. Once I had a couple calculations I expanded it to add more. Using the package I…
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…
The Towers of Hanoi are a classic children’s puzzle. With 3 pegs and a set of disks of differing sizes, the goal is to move a stack of disks (arranged with largest on the bottom to smallest on top) from one peg to another moving only one disk at a time, never putting a larger…
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…
The Water Jugs are a classic puzzle going back centuries with many variations. While the specific numbers involved may change from one telling to the next the general form is one small jug (cup, barrel, bucket, etc.) and one large one. Each with an integer capacity of units (liters, gallons, ounces, etc.) The goal of…
I recently ran into a strange bug where I spent a lot of time banging my head trying to figure out why my recursion wasn’t working, causing ORA-32044: cycle detected while executing recursive WITH query errors to show up. After simplifying the query down to a single counter column I eventually discovered the problem was…
Yesterday, Connor McDonald posted an interesting article about a math puzzle he received asking him to calculate the integral of a function over a range. He posted two variations using the Trapezoidal Rule. In his post he mentions the Riemann sums method and I thought it would be interesting to explore that option as well.…
21c was released for Linux a little over a week ago. I downloaded the zip file, unzipped it, and created an empty container database.Then I wanted to upgrade one of my 19c DBs but I didn’t want to lose the 19 version. I wanted to have a before and after image to compare. This is…
Like many, when I read the news of Joel’s passing I just couldn’t believe it. I spoke with Joel at several conferences, a local user group, as well as a visit to my workplace to talk about APEX (of course.) While his technical chops were without question – the most inspiring thing about Joel was…
Most companies will have some standard for password complexity they follow and the Oracle database has long supported enforcement of those standards by attaching password verification functions, owned by SYS to profiles. These standards are so common and so important Oracle not only allows; but forces you to put those user-defined objects into their SYS…
A few weeks ago I posted an article about solving a combinatorics puzzle using SQL. In comments that followed, Iudith Mentzel mentioned another interesting puzzle involving combinations: the Four Weights puzzle. At the time I thought it might be an interesting problem to tackle with SQL. Last night I was up monitoring systems during some…
A couple of great things about APEX charts are Dynamic Actions on the region and the easy to use Link option when clicking on a chart element. However, if you use a dynamic action, it doesn’t know about the fields within the clicked element. If you use the link option, it does allow you to…
While the Instant Client is a popular client installation for many applications, very often developers and admins become frustrated by the conspicuous absence of the TNSPING utility. Developers using jdbc thin clients may also miss having this handy tool at their disposal. Fortunately there are ways to replicate some of the functionality of TNSPING using…
Creating db links between Oracle Cloud Autonomous databases is similar in concept to connecting to a cloud db from on-premises; but the steps are necessarily different because you don’t have access to the database server’s file system to adjust the sqlnet.ora or tnsnames.ora files. From within the Oracle Cloud interface select the database you want…