See you at KScope!

After a 7-year hiatus from speakin at any conferences, I'll be presenting at KScope 26 in Denver.

seanstuber.com

Solving Performance Problems with a Touch of Magic

When I'm tackling a complex performance problem spanning multiple programs, machines, services, etc. I like to wave a magic wand, invoke mystic elves, or call on friendly gnomes to assist me and simply make part of the problem disappear. That particular step now takes zero time, thereby removing any impact it had on the overall process performance. Voila... problem solved! Right?

seanstuber.com

Assertions in 26ai

I've started keeping configuration information for Oracle's Connection Manager in tables and then generating the cman.ora file needed for each listener. One of the quandaries I ran into was trying to enforce a rule where you could not define invited nodes for registration if you had registration checking disabled. It is technically legal to do so; but for our own internal rules it didn't make sense to allow it.

seanstuber.com

Advent of Code 2025- Day 11

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 in the targets of the initial line "you: bbb ccc" thus connecting those rows.

seanstuber.com

Advent of Code 2025- Day 9

Movie Theater Part 1 was pretty straightforward. I extract the X and Y coordinates from each line and then using a self-join, I compare the areas of each possible rectangle. As with many other, similar, self-joins, I use the b.seq > a.seq filter to eliminate redundant, commutative calculations (i.e. rectangles of A & B corners are the same as rectangles of B & A corners.)

seanstuber.com

Advent of Code 2025- Day 8

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 it easier for me to implement than if I had to write…

seanstuber.com

Advent of Code 2025- Day 7

Laboratories This one was a little misleading, but that's probably more due to me assuming too much going in than any tricky wording. My first thought was this would be a recursion puzzle because of the tree nature and there hadn't been one yet; but I ended up not needing recursion to solve it. It probably would have been harder to get right if I had pursued that path.

seanstuber.com

Advent of Code 2025- Day 6

Trash Compactor Like Day-5, I accepted a slight efficiency hit by reading the data twice for logical convenience. I read the numbers as one CTE and then read the corresponding operators as a second CTE. Since the data is irregularly spaced I used regular expression to collapse multiple spaces into a single space as a delimiter between values. Then using STR2TBL I split each row into a collection of numbers.

seanstuber.com

Advent of Code – Day 5

Cafeteria I accepted a small inefficiency in part 1 by reading through all of the data twice in order to come up with two data sources. One for the ranges of fresh ids, and the other the list of ids to check. Counting the number of fresh ids was simply checking if each id fell between the low and high value of any fresh-ranges, and that's accomplished easily enough by an inner join between the rows of ids to the rows of ranges.

seanstuber.com

Advent of Code 2025 – Day 4

Printing Department The first map puzzle of the year. Part 1 was a simple count; just loop through each position and see how many neighboring rolls it has, if it's less than 4, increment a counter. Return the counter when done. DECLARE v_map advent.t_map := advent.data2map('advent2025-4sample'); v_neighbor INTEGER; v_prevcnt integer := null; v_cnt INTEGER := 0; BEGIN FOR y IN 1 ..

seanstuber.com

Advent of Code 2025 – Day 3

I was kind of disappointed in day 3 because it's the same puzzle twice. So one solution solves both parts 1 and 2, just changing the length from 2 to 12. I start by looping from the first digit (most significant) down to the least significant digit. So, for a 2-digit joltage, I find the "tens" first (i.e. 101), then the "ones" (100).

seanstuber.com

Advent of Code 2025 – Day 2

Day 2 was both easy and hard. Easy because it was easy to understand and hard because I knew there was a trick that would make an efficient solution for part 2 but I couldn't remember it. For part 1, I just divide the line of ranges into rows and then use regexp to pull the low and high value for each range.

seanstuber.com

26ai coming to on-prem in January!

Many of had given up hope, but an official announcement has been made and I am, like many others, excited to start installing it on our Linux systems. You can read the official announcement here... Only the Linux x86 platform is mentioned specifically in that Oracle blog, but the MOS Doc 742060.1 also indicates AIX and Windows releases are scheduled (albeit tenatively) for next year too!

seanstuber.com

Advent of Code 2025 – Day 1

I'm late getting started on the puzzles this year, I'll try to catch up and see how many I can do before I break for the holidays and family time. Secret EntrancePart 1 was fairly obviously about modulo math; but the MOD function in Oracle works oddly with negative numbers. By most mathematical definitions -3 MOD 100 would be 97; but MOD(-3,100) will yield -3 with Oracle SQL, but MOD(-103,100) will also yield -3, so it is still modulo of a sort, but on a range of -99 to 99, not 0 to 99.

seanstuber.com

Creating APEX report with rolling set of columns #JoelKallmanDay

The last several years I've participated in the Joel Kallman Day tributes with articles about SQL and PL/SQL. While there are no requirements for article topics, this year I wanted to contribute something within the framework Joel was best known for... APEX. Many reports aggregate data over time periods and will usually return different dates and times as distinct rows. In some reports though, it is more useful to show a rolling window of time.

seanstuber.com

Duplicating an IG does not duplicate Automatic Row Processing DML

When you duplicate an Interactive Grid in APEX, if that region has a process to perform Automatic Row Processing DML; the new, duplicated region, will not have a DML process created for it. You can create a DML process for the new region from scratch or duplicate that of the original Interactive Grid and then update the Editable Region property of the new process to the name of the new Interactive Grid.

seanstuber.com

Using SQL to derive dice distributions in D&D

In a previous article (Bringing APEX and PL/SQL to D&D) I used a package with formulas of predetermined distributions for rolling multiple dice. In this article I will show how I came up with those formulas. In each case of rolling multiple 20-sided dice, I didn't know before hand what the results would be; but I assumed there would be patterns I could write as expressions.

seanstuber.com

Bringing APEX and PL/SQL to D&D

While there are plenty of opportunities in the real world to use APEX; I felt the need to bring APEX into my gaming. The idea started with a conversation in mid-game about some tactical decisions in a game of Dungeons & Dragons. That is, how should we go about tackling an upcoming combat. After awhile it turned into some meta-gaming of the mathematical implications of various options.

seanstuber.com

Interesting bug with nondeterministic functions

I was working on a little simulation using random numbers and found I was getting odd results from my query. After spending a while dissecting it trying to find my error I found the problem was in the database itself. I was able to replicate the problem on Live SQL using a simple test case with both 19c and 23ai…

seanstuber.com

New Certification and FeedSpot Recognition

I successfully completed my Oracle AI Vector Search certification. I was also elated to hear FeedSpot has, again, selected my blog as one of the Top 100 Oracle Blogs on the web.

seanstuber.com

PL/SQL package for 32-bit xoshiro/xoroshiro pseudorandom number generators

Wrapping up my series on the, xoshiro/xoroshiro algorithms, in this article I present a package to return 32-bit values using the following variants. xoroshiro64* xoroshiro64** xoshiro128+ xoshiro128++ xoshiro128** The numbers in the names refer to the size of the state array that each method operates on. The 64s operate on two 32-bit values, while the 128s operate on four 32-bit values.

seanstuber.com

PL/SQL package for 64-bit xoshiro/xoroshiro pseudo-random number generators

Continuing in the path of previous two posts, the package below supports all of the xoshiro/xoroshiro algorithms returning 64-bit values. xoroshiro128+ xoroshiro128++ xoroshiro128** xoshiro256+ xoshiro256++ xoshiro256** xoshiro512+ xoshiro512++ xoshiro512** xoroshiro1024* xoroshiro1024++ xoroshiro1024** 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: Choose the algorithm mode (the package defaults to xoshiro256++)

seanstuber.com

Bit manipulation of large numbers in pl/sql

As I mentioned in my previous post I had to write my own functions to perform some bit operations on numeric values. While looking into some other pseudo-random number generators I ran into a few unexpected problems. The first problem is actually a documented restriction; but one I had not encountered previously. The BITAND function only supports numbers up to 2127-1.

seanstuber.com

Implementing xoshiro256** in PL/SQL

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, rock-solidgenerators". Like the Mersenne Twister, the original source is written in c and utilizes numerous bit manipulations to generate new values from the prior ones.

seanstuber.com

Advent of Code 2024 – Day 4

Ceres Search Day 4 took a different turn, we're still parsing through text to find special values, but now we need to parse horizontally, vertically, and diagonally through a grid of text looking for "XMAS", which could appear forwards or backwards (SAMX.) For this I built a two-dimensional map of the text. As I read each line, looking for horizontal instances of XMAS was trivial, I just used REGEXP_COUNT to look for XMAS and SMAX.

seanstuber.com

Advent of Code 2024 – Day 3

Mull It Over For Day 3, we're given sequences of fictitious code and we have to extract the multiplication commands from it; which will be of the form "mul(x,y)" where x and y are integers. So, using a regular expression makes finding the valid substrings easy. Once those are extracted, it's just a matter of extracting the two numeric parameters from within the parentheses, multiplying them, and then summing the products.

seanstuber.com

Advent of Code 2024 – Day 2

Red-Nosed Reports For Day 2, we have to find sequences of numbers that are safe to process. For part 1, "safe" means the numbers are in order, either increasing or decreasing, and the step between each is never more than 3. My approach to this was to pivot each row of numbers into a sequence, which would then let me use the LAG function to compare the numbers in order.

seanstuber.com

Advent of Code 2024 – Day 1

Historian Hysteria In this puzzle we're given 2 lists of numbers that should be paired together such that the smallest value of each list form a pair, then the next smallest, and so on until we pair the largest values from each list together. To solve it, I read the values from each line of the input file, parsing the two numbers into A and B columns corresponding to the values of the left and right lists respectively.

seanstuber.com

Day 19 - Advent of Code 2024

adventofcode.com