Fear No SQL
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…
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…
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…
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…
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…
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…
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. I added a new function to the ADVENT package, MAPCHAR. All it does is…
Lobby 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…
Gift Shop 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…
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…https://blogs.oracle.com/database/oracle-ai-database-26ai-coming-soon-for-linux-x86-64-on-premises-platforms Only the Linux x86 platform is mentioned specifically in that Oracle blog, but the MOS Doc 742060.1 also indicates AIX…
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…
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…
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…
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…
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…
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…
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. 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…
Continuing in the path of previous two posts, the package below supports all of the xoshiro/xoroshiro algorithms returning 64-bit values. 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: For example… SQL> BEGIN 2 xoshiro.set_mode(‘xoroshiro128++’); 3 xoshiro.set_state(xoshiro.seed_tab(12345, 67890));…
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…
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,…
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…
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…
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,…
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…
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…