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.
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. 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…
PL/SQL package for 64-bit xoshiro/xoroshiro pseudorandom number generators
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));…
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…
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,…
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…
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…
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,…
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…
Exploring the PAIRS OF iterator
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…
How To Implement Glicko-2 In PL/SQL #JoelKallmanDay
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 PL/SQL Dangling Predicates in CASE expressions
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…
Choosing OpenAI model with Oracle 23ai SELECT
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.…
Using MLE JavaScript function to iterate JSON Keys in SQL
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…
Using a Java function to iterate large JSON with many keys.
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…
How to create a PL/SQL function to iterate JSON Keys for SQL
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…
How to use and view comments to your parameter changes
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 #…
Comparing 19c vs 21c JSON key lists
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…
How to recursively search JSON with PL/SQL
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.…
Calculating Skewness of a Population
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…
Calculating Skewness of a Sample #JoelKallmanDay
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…
23ai adds PING to SQL*Plus
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…
Pipelined PL/SQL and Java with ODCI
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…
Working with Table Functions in SQL
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…
Exiting the OJVM in your db session
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…
Oracle 23ai interval aggregates
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)…
Oracle 23ai Boolean support in SQL
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…
Date/Timestamp Cheat Sheet
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…
Finding dates of holidays using PL/SQL
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…
Calculating Binomial Distribution Probabilities in PL/SQL
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.…
Large Number Combinatoric calculations in PL/SQL
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.…
Let NULL work for you #JoelKallmanDay
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…
Mean, Median, and Mode of summarized data
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,…
Calculating Hypergeometric Distribution probability in PL/SQL
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…
Working with Collections in SQL
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…
SQL and Set Theory
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, Part 10: Pulling it all together
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, Part 9: Alternative content
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, Part 8: HTML-formatted content
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, Part 6: MIME Content
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, Part 7: UTL_SMTP – Attachments
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…
Free Falling with PL/SQL
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…
Sending Email with Oracle, Part 5: UTL_SMTP – Authentication (manual)
Sending Email with Oracle If using an older version of the Oracle database (11gR1 or earlier) the UTL_SMTP package does not have authentication APIs built in. You may also run into a situation where a server implements a mechanism that UTL_SMTP does not support. You still have options though, by implementing some of the authentication…
Sending Email with Oracle, Part 3: UTL_SMTP – Introduction
Sending Email with Oracle UTL_SMTP UTL_SMTP has a richer functionality than the UTL_MAIL or APEX_MAIL packages; but that extra versatility comes with more complicated syntax. Before digging into the details of how the package works I’ll first show a short UTL_SMTP example, simply sending “Hello World!” to two recipients. DECLARE crlf CONSTANT VARCHAR2(2) := UTL_TCP.crlf;…
Sending Email with Oracle, Part 2: APEX_MAIL
Sending Email with Oracle APEX_MAIL The APEX_MAIL package is a more robust version of what you’ll find with UTL_MAIL, but it is only available when APEX is installed. It also requires pre-configuration of the smtp server, port, authentication, wallets, and encryption. These features require APEX-administrative privileges to set. Since APEX might not be installed and…
Sending Email with Oracle, Part 4: UTL_SMTP – Authentication (API)
Sending Email with Oracle Surprisingly, the original SMTP protocol provided no means of authenticating; but with the rise of spam engines and malicious users it quickly became a necessary component and was later added with addendum RFC’s. Despite authentication being part of the standard at the time, when Oracle introduced the UTL_SMTP package in version…
Sending Email with Oracle, Part 1: UTL_MAIL
Sending Email with Oracle UTL_MAIL The UTL_MAIL package is an interesting addition to the database functionality because it came after UTL_SMTP and could have been added to the existing functionality, but was instead split off into its own package. The package is not installed by default either. If you wish to use UTL_MAIL, your DBA…
SQL vs Towers of Hanoi
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…
LDAP to JSON function #JoelKallmanDay
I’ve found myself needing to search LDAP data more often and each time I do it’s been sort of obnoxious to have to rewrite variations of mostly similar DBMS_LDAP routines. Frequently I then need to use that data in a SQL query of some sort to combine it with other data found in one or…
Solving the Water Jug puzzle with SQL
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…