Sean D. Stuber

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…

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…

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…

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…

Creating db links to an Oracle Cloud ADB database is mostly the same as you would between two on-premises databases. The key difference is the requirement to use a wallet for the SSL communication. From within the Oracle Cloud interface select the database you want to link to, go to the DB connection tab and…

It should come as no surprise DBAs or other users with similarly elevated privileges have access to many objects that can give them visibility into the nature of the database, including its version. V$INSTANCE and DBA_REGISTRY are commonly used views to see not only the version number; but other useful information as well. But, what…

Developers coming from a procedural coding background are familiar with traditional IF-THEN-ELSE constructs, possibly extended to SWITCH or CASE blocks that group a series of conditions. Depending on the language, those could either evalute the conditions until the first TRUE is found or until the first result breaks out. They might also be comfortable with…

For me, TRUNC is one of the handiest Date functions available.  If you work with time-based data a lot you’ll frequently find a home for it in your queries.  For example, find all objects that were modified yesterday: select * from all_objects where last_ddl_time >= trunc(sysdate)-1 and last_ddl_time < trunc(sysdate) TRUNC isn’t limited to just…

The 12-Coins is a classic balance puzzle where the goal is find a fake coin from one of 12 using only 3 trials on the balance scale. Furthermore, once you find the fake coin you should be able to determine if it is lighter or heavier than the real coins. The solution involves dividing the…

Several months ago I used the classic 8-Queens chess puzzle as an illustration of using a little bit of math to determine intractable problems. In that article I teased a code solution for the puzzle but never followed up… until now. There are 92 distinct solutions, including rotations and reflection duplicates. The package’s demo procedure…

My wife has a nutcracker collection. One of them includes a pair of cubes for use as a counter. One of the cubes has the digits 0,1,2,3,4,5 on the faces. The other cube has the digits 0,1,2,6,7,8. The 6 is invertible to be used as a 9, thus allowing for 7 digits on 6 faces.…

An INTERVAL YEAR TO MONTH, can have up to 9 digits of year precision. By default, a column or pl/sql variable will have 2 digits. The month portion of the interval is always limited to 2 digits. The year limits can be changed by specifying (0-9) in the declaration. select INTERVAL ‘123-06’ YEAR(3) TO MONTH…

An INTERVAL DAY TO SECOND, can have up to 9 digits of sub-second precision (nanoseconds.) By default, a column or pl/sql variable will have 6 digits (microseconds.) In addition to the subsecond precision, a default INTERVAL DAY TO SECOND will also be limited to 2 digits in the day counter. These limits can be changed…

The interval types are often under utilized, even completely unknown to many developers. They can provide a wealth of functionality though when used properly. Within the SQL context there are two types of intervals: INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH. The names indicate the maximum and minimum units of measure. That is,…

Using Dates for dates and the dangers of Implicit conversions As mentioned in previous articles, a common mistake is choosing a data type other than a Date or Timestamp to hold time-based data.  Numeric values like 20120424  or even worse 04242012.  While both of these are human readable as yyyymmdd and mmddyyyy respectively; they have…

In my previous article I stressed the importance of using correct data types. However in some cases you may be forced to choose one type when you really need to use two or more. Also, in some cases, you may think you’re using consistent data types but Oracle will insist on changing them anyway, using…

One of the fundamental aspects of many programming languages is the notion of data types. SQL and PL/SQL are no exception. This article is not an argument one way or the other on whether strongly-typed languages are superior to weakly-typed, or untyped languages. The argument is moot. If you’re working with an Oracle database, you…

Contemplating the beginning and ending of time can be a complicated exercise in the mathematics of cosmology. Within the realms of an Oracle database though it’s much easier to get our heads around. By definition within Oracle, the beginning of time is -4712-01-01 00:00:00.The end of time is 9999-12-31 23:59:59.999999999. Thus all of time, past…

I might (eventually) post various implementations of the 8 Queens problem; but that solution is not what this article is about. Instead I want to focus on the mathematical nature of the problem, how it relates to SQL, and how a little bit of math-think can help identify problems of intractability which translate into performance…

In a general sense you could say almost all SQL issues are in some way related to misapplied predicates. In this article I’ll focus on two classes of problems. The first would be queries where there is confusion between join predicates and filtering predicates. The second type of problem is with the predicates that are…

I recently ran into an interesting situation where a standalone database was changed to a higher SLA requirement and altered to run across a cluster with RAC One Node. The application running in that database had jobs created with instance affinity set. There was no harm in doing so with the standalone system, but there…

The first version of Oracle I ever touched in any way was, I think, an installation of Oracle 6. The first version I actually did development on was Version 7 and thus beings my tale… 7 I had already been writing queries for a few years, so I was fairly comfortable with SQL when 7…