Fear No SQL
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…
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…
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 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 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 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 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 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…
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 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 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 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 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 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…
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…
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…
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…
I recently ran into a strange bug where I spent a lot of time banging my head trying to figure out why my recursion wasn’t working, causing ORA-32044: cycle detected while executing recursive WITH query errors to show up. After simplifying the query down to a single counter column I eventually discovered the problem was…
Yesterday, Connor McDonald posted an interesting article about a math puzzle he received asking him to calculate the integral of a function over a range. He posted two variations using the Trapezoidal Rule. In his post he mentions the Riemann sums method and I thought it would be interesting to explore that option as well.…
21c was released for Linux a little over a week ago. I downloaded the zip file, unzipped it, and created an empty container database.Then I wanted to upgrade one of my 19c DBs but I didn’t want to lose the 19 version. I wanted to have a before and after image to compare. This is…
Like many, when I read the news of Joel’s passing I just couldn’t believe it. I spoke with Joel at several conferences, a local user group, as well as a visit to my workplace to talk about APEX (of course.) While his technical chops were without question – the most inspiring thing about Joel was…
Most companies will have some standard for password complexity they follow and the Oracle database has long supported enforcement of those standards by attaching password verification functions, owned by SYS to profiles. These standards are so common and so important Oracle not only allows; but forces you to put those user-defined objects into their SYS…
A few weeks ago I posted an article about solving a combinatorics puzzle using SQL. In comments that followed, Iudith Mentzel mentioned another interesting puzzle involving combinations: the Four Weights puzzle. At the time I thought it might be an interesting problem to tackle with SQL. Last night I was up monitoring systems during some…
A couple of great things about APEX charts are Dynamic Actions on the region and the easy to use Link option when clicking on a chart element. However, if you use a dynamic action, it doesn’t know about the fields within the clicked element. If you use the link option, it does allow you to…
While the Instant Client is a popular client installation for many applications, very often developers and admins become frustrated by the conspicuous absence of the TNSPING utility. Developers using jdbc thin clients may also miss having this handy tool at their disposal. Fortunately there are ways to replicate some of the functionality of TNSPING using…
Creating db links between Oracle Cloud Autonomous databases is similar in concept to connecting to a cloud db from on-premises; but the steps are necessarily different because you don’t have access to the database server’s file system to adjust the sqlnet.ora or tnsnames.ora files. From within the Oracle Cloud interface select the database you want…
Creating db links to an Oracle Cloud ADB database is mostly the same as you would between two on-premises databases. The key difference is the requirement to use a wallet for the SSL communication. From within the Oracle Cloud interface select the database you want to link to, go to the DB connection tab and…
It should come as no surprise DBAs or other users with similarly elevated privileges have access to many objects that can give them visibility into the nature of the database, including its version. V$INSTANCE and DBA_REGISTRY are commonly used views to see not only the version number; but other useful information as well. But, what…
I’ve released version 2.3 of my service name maintenance package for LDAP servers implementing the OID schema. I’ve exposed the get_entry function both as a stand alone function the opens, reads, and closes a connection to the ldap server, and as an API accepting an already established connection and using it. Along with the function…
Developers coming from a procedural coding background are familiar with traditional IF-THEN-ELSE constructs, possibly extended to SWITCH or CASE blocks that group a series of conditions. Depending on the language, those could either evalute the conditions until the first TRUE is found or until the first result breaks out. They might also be comfortable with…
A common problem often encountered in application development is the lack of bind variables; thus requiring additional cpu resources due to the continual hard parsing of statements. The advantages of bind variables have been discussed by many experts over the years. An excellent summary by Chris Saxon can be found here. The point of this…
For me, TRUNC is one of the handiest Date functions available. If you work with time-based data a lot you’ll frequently find a home for it in your queries. For example, find all objects that were modified yesterday: select * from all_objects where last_ddl_time >= trunc(sysdate)-1 and last_ddl_time < trunc(sysdate) TRUNC isn’t limited to just…
The 12-Coins is a classic balance puzzle where the goal is find a fake coin from one of 12 using only 3 trials on the balance scale. Furthermore, once you find the fake coin you should be able to determine if it is lighter or heavier than the real coins. The solution involves dividing the…
Several months ago I used the classic 8-Queens chess puzzle as an illustration of using a little bit of math to determine intractable problems. In that article I teased a code solution for the puzzle but never followed up… until now. There are 92 distinct solutions, including rotations and reflection duplicates. The package’s demo procedure…
My wife has a nutcracker collection. One of them includes a pair of cubes for use as a counter. One of the cubes has the digits 0,1,2,3,4,5 on the faces. The other cube has the digits 0,1,2,6,7,8. The 6 is invertible to be used as a 9, thus allowing for 7 digits on 6 faces.…
An INTERVAL YEAR TO MONTH, can have up to 9 digits of year precision. By default, a column or pl/sql variable will have 2 digits. The month portion of the interval is always limited to 2 digits. The year limits can be changed by specifying (0-9) in the declaration. select INTERVAL ‘123-06’ YEAR(3) TO MONTH…
An INTERVAL DAY TO SECOND, can have up to 9 digits of sub-second precision (nanoseconds.) By default, a column or pl/sql variable will have 6 digits (microseconds.) In addition to the subsecond precision, a default INTERVAL DAY TO SECOND will also be limited to 2 digits in the day counter. These limits can be changed…
The interval types are often under utilized, even completely unknown to many developers. They can provide a wealth of functionality though when used properly. Within the SQL context there are two types of intervals: INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH. The names indicate the maximum and minimum units of measure. That is,…
When working with dates and timestamps, an almost inevitable tasks will be working with different time zones and daylight saving transitions. Even if all of the data you use is with a single local time, chances are you will eventually interface with a system that uses a different one. Assumed Time zones If I have…
Before you can dig into your data with SQL and PL/SQL you must first get connected to your database. While there are many architectures, tools, and drivers for connecting such as JDBC, ODBC, ROracle, node-oracledb, Oracle Provider for OLE DB, OCI, cx_Oracle, etc. They essentially all boil down to just two methods: either the Oracle…
Using Dates for dates and the dangers of Implicit conversions As mentioned in previous articles, a common mistake is choosing a data type other than a Date or Timestamp to hold time-based data. Numeric values like 20120424 or even worse 04242012. While both of these are human readable as yyyymmdd and mmddyyyy respectively; they have…
In my previous article I stressed the importance of using correct data types. However in some cases you may be forced to choose one type when you really need to use two or more. Also, in some cases, you may think you’re using consistent data types but Oracle will insist on changing them anyway, using…
One of the fundamental aspects of many programming languages is the notion of data types. SQL and PL/SQL are no exception. This article is not an argument one way or the other on whether strongly-typed languages are superior to weakly-typed, or untyped languages. The argument is moot. If you’re working with an Oracle database, you…
Contemplating the beginning and ending of time can be a complicated exercise in the mathematics of cosmology. Within the realms of an Oracle database though it’s much easier to get our heads around. By definition within Oracle, the beginning of time is -4712-01-01 00:00:00.The end of time is 9999-12-31 23:59:59.999999999. Thus all of time, past…
I might (eventually) post various implementations of the 8 Queens problem; but that solution is not what this article is about. Instead I want to focus on the mathematical nature of the problem, how it relates to SQL, and how a little bit of math-think can help identify problems of intractability which translate into performance…
In a general sense you could say almost all SQL issues are in some way related to misapplied predicates. In this article I’ll focus on two classes of problems. The first would be queries where there is confusion between join predicates and filtering predicates. The second type of problem is with the predicates that are…
I’ve had multiple conversations over the past few years that were variations on a theme. They generally go something along the lines of these… “We’re starting a new project and we plan to use APEX. How are we going to keep users from getting into the database and mucking with stuff?” “We’re starting a new…
I recently ran into an interesting situation where a standalone database was changed to a higher SLA requirement and altered to run across a cluster with RAC One Node. The application running in that database had jobs created with instance affinity set. There was no harm in doing so with the standalone system, but there…