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.

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.

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.

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.

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…

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.

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.

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++)

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.

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.

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.

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.

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.

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.