Fear No SQL
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…
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…
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…
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…
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…
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…
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…
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,…
The trimming functions in Oracle frequently are used by many developers; but not all know the full set of options available and how they compare to each other. Sometimes I need to remind myself so this post is as much for me as for others. I hope you find it helpful. The LTRIM and RTRIM…
Recently I was asked how to bind an R variable to a SQL statement executed with ROracle. In this case it wasn’t a simple “select * from table where x = :b1 and y = :b2.” You can do those quite easily with a data.frame with only one row in it using dbGetQuery(connection, sqlstatement, data.frame)…