Fear No SQL
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…
The first version of Oracle I ever touched in any way was, I think, an installation of Oracle 6. The first version I actually did development on was Version 7 and thus beings my tale… 7 I had already been writing queries for a few years, so I was fairly comfortable with SQL when 7…
I’ve used OpenLDAP for my name service at home for several years. I don’t have a ton of databases, but most of the pcs have XE installed and my sandbox server always has multiple Enterprise Edition databases of various versions for me to try things. It wouldn’t be too hard to maintain all of them…
I’ve made several updates to my package for maintaining name resolution entries in LDAP. The search calls have been modified to be both more accurate and more efficient. Previously I would use the one-level search under the OracleContext path whether the search was for a specific entry or all of them. Now, if you search…
The Oracle Change Notification or Continuous Query Notification functionality is a useful means of triggering events in remote sessions when a transaction commits. Sometimes though a client may set up a registration and then disappear leaving orphaned registrations. Using the DBMS_CHANGE_NOTIFICATION package (documented by it’s synonym DBMS_CQ_NOTIFICATION) these old entries can be removed easily with…
Today, Oracle announced both the “Oracle Spatial and Graph” as well as the “Oracle Machine Learning” (formerly Advanced Analytics) database options are now included at no additional cost for on-premises installations of Enterprise Edition and Enterprise Edition on Engineered Systems. These options provide a wealth of functionality that were formerly extra cost options. You can…
I was asked about some strange behavior querying a view where the results of a single row seemed to inexplicably change depending on the where clause. After a few minutes of looking around and creating a simplified test case I was able to produce similar odd behavior. Before digging in, let me start by showing…
I added an appendix for special user accounts that don’t follow the normal password rules to my paper describing the password hashing and storage. Some users have no hash at all but are instead stored with fixed values. ANONYMOUS – SYS.USER$.PASSWORD has a value of exactly 16 spaces. The SPARE4 column is NULL. Since the…
After an ORDS upgrade an APEX application stopped displaying any images from its uploaded static files. If you attempted to view the image directly you’d get an HTTP 404 error. 404 Not Found-The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been…
In my previous post I mentioned the difficulty in finding consistent implementations of the NYSIIS algorithm. Upon further reading, it appears the ambiguity in some of the rule descriptions is a known problem. The issue was documented as far back as January 1977 in the National Bureau of Standards Special Publication 500-1, page 24: “The…
What started as a simple question has led me down an interesting path. I was asked to help with a text searching question and the developer wanted to know if SOUNDEX would work for their name searching. That kind of work is exactly what SOUNDEX is for so I agreed it could be used but…
Frequently, when dynamic sql is used it takes a form similar to this: v_sql := ‘select ‘ || CASE WHEN choice1 IN (‘A’, ‘B’) THEN ‘col1, col2’ WHEN choice = ‘C’ THEN ‘col2,col3’ ELSE ‘col4,col5’ END || ‘ from ‘ || CASE WHEN choice2 IN (‘X’, ‘Y’, ‘Z’) THEN ‘table1’ ELSE ‘table2’ END || ‘…
A few years ago I wrote a small article about finding the various name of a database through different means. One of which was SYS_CONTEXT with the USERENV namespace. At the time (April 2016) the latest version of the Oracle database as 12cR1 where a query like this: select SYS_CONTEXT(‘userenv’,’db_name’) from dual; Would return the…
As we approach the end of Daylight Saving Time in the United States for 2019, I thought it would be a good time to double check on some of the job schedules defined in the database. If we have a job scheduled to run at 4pm every day, we don’t want it to become a…
A common need when processing numbers is to adjust values up or down, such as rounding to nearest integer or removing the fractional part of a number. Oracle has a variety of means of doing so. Unfortunately sometimes developers will confuse functions that produce similar results under some circumstances only to be surprised when they…
Normally, when syntax is listed as “optional” there should be no functional difference between the inclusion or omission of that syntax. However, with the JSON_OBJECT function, the optional “FORMAT JSON” clause not only changes functionality if you include it, it actually causes the resulting json output to be invalid! This easily verified with a simple…
From the APEX Developer’s Guide: When you export an application with the Access Control feature, the application roles, Administrator, Contributor, and Reader, are exported. However, the users assigned to these roles are not exported. [spelling correction “your”-“you” mine] If the export option won’t put the users into a script for deployment, the obvious question then…
One of most common uses of Oracle’s SQL*Plus is running text file scripts; either using START or, more commonly, the @ or @@ commands. Most of the time START and @ are interchangeable for initiating the execution of a script. @@ behaves similarly but with documented path differences. So, while they appear to be mostly…
A few weeks ago I received a question about how to generate DDL for all the tables, procedures, functions, and packages of a set of schemas. Furthermore, the asker wanted each object’s script to be in a distinct file within a directory hierarchy. Specifically, given a base directory, each schema should have its own subdirectory…
Some data is more easily absorbed in visual form. Trending and relative comparisons in particular can often be digested at a glance when presented as a line or bar chart. While SQL itself doesn’t have any graphing capabilities; using some of the Unicode symbol characters it is possible to create simple charts within a text…
Thank you to all that attended my presentations this year both live or in the virtual conference. As promised here are the links to my slides. https://www.dropbox.com/s/d7kjh5rp70ucul0/2019_352_Stuber_ppt.pptx?dl=0 https://www.dropbox.com/s/lqqhb6m8b3vrv0p/2019_354_Stuber_ppt.pptx?dl=0 For session 354, I mentioned expanded versions of some of the dictionary queries. Those maybe found at the following links… https://seanstuber.com/2018/03/08/who-is-using-temp-and-what-are-they-doing/ https://seanstuber.com/2018/03/04/who-is-blocking-whom-and-what-are-they-doing/ Thank you again to all…
One of the great features of Oracle is the ability to create a table function. That is, a function returning a collection type that can be used as the source for a query. These functions then allow a developer to provide a simple SQL api to query rows and columns from a variety of data…
Recently, I had a satisfying conclusion to a series of questions that came in randomly over the course of a few weeks. First the developer was trying to do recursion. I pointed to documentation and gave a couple of small examples showing CONNECT BY and recursive WITH clauses. SELECT LPAD(‘ ‘, LEVEL * 2) ||…
Today I received a request that had me using the COLLECTION function to aggregate values for different categories. However, it didn’t take too long to find extra rows creeping into my results. To my surprise I found using DISTINCT within a COLLECT works, unless you also try to order the results with an ORDER BY…
Oracle provides a great set of tools called “gateways” to connect an Oracle database to other data sources such as SQL Server or DB2. Most of these gateways are separately licensed though. There is a no-cost ODBC-based gateway but that might not fit your needs and you may want to explore using other technologies to…
What is ASH? AWR and Activity Types Average Active Sessions Graphical Visualizations (this page) Up to this point in my ASH series all of the output was simply rows and columns of data, sometimes augmented with an ascii, text based chart. This time I’ll explore a few different tools to construct better visualizations. One of…
Today I was asked how to convert iyyy and iw values into dates, in particular how to convert the day-of-week value since there is no “id” format mask. As it turns out, it wouldn’t matter if there was because the “i” format masks are not supported in TO_datetime functions. They can only be used in…
What is ASH? AWR and Activity Types Average Active Sessions (this page) Graphical Visualizations In the first 2 chapters of this series I queried the ASH data with respect to distinct sample times. While this is helpful maximizing the granularity of the analysis, it can sometimes be too much detail, too many data points for…
If you’re familiar with java, c, c# (or, I’m sure other languages as well) then you’ve probably encountered functions that allow a variable number of input parameters without requiring a fixed set of overloading. Java int my_variable_function(String str, int …intlist) c int my_variable_function(char *str, …) c# int my_variable_function(string str, params int[] intlist) PL/SQL does not…