Fear No SQL
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…
What is ASH? AWR and Activity Types (this page) Average Active Sessions Graphical Visualizations Previously I showed a few queries using (G)V$ACTIVE_SESSION_HISTORY, but only mentioned the DBA_HIST_ACTIVE_SESS_HISTORY view. Here I’ll extend the query patterns into this historical repository. Where V$ACTIVE_SESSION_HISTORY is a once-per-second snapshot of sessions from memory and kept in memory, the DBA_HIST_ACTIVE_SESS_HISTORY data…
While testing some date/time functions today I came across an interesting quirk in Oracle’s Gregorian calendar and different date representations. In the Gregorian calendar, the day after October 4, 1582 is October 15, 1582 (not all countries adopted the calendar immediately, so they have different gaps, but Oracle observes the calendar as it was originally…
What is ASH? (this page) AWR and Activity Types Average Active Sessions Graphical Visualizations The Oracle database captures session activity approximately once each second. This data can then be used to identify usage patterns and performance problems. Many performance experts have written very good suites of scripts and tools to query, analyze, and chart the…
Recently I received a call that an APEX application was experiencing poor performance. They asked me to investigate what was happening on the database or in APEX itself. First, I ask if they have any application logs that give some indication of where the problems might be. The answer came back no. There was no…
Recently I’ve been working with a data source that generates a new file each day for me to import. That seemed simple enough, I created an external table to read the file and then run an insert statement to load the data into a target table. However, the source files sometimes overlap each other. That…
If you’re not into PL/SQL, substitute Jonathan Lewis, Tanel Poder, Maria Colgan, or any other technology great you might respect. Doesn’t even need to be Oracle related. The answer still, quite simply, is yes. First – it’s not a competition. If it were, then yes, I concede to the guy that literally wrote book on…
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data Browsing Attributes Timeouts Modifying Data (this page) Modifying data in the LDAP hierarchy The DBMS_LDAP package allows you to write as well as read data from the directory. However, it does not provide an API for manipulating the schemas within the tree. That is, you…
Today I was asked about a query from an Audit Vault job that was failing. Unfortunately, the job didn’t report any useful error messages to explain the failure. After some digging the problem was narrowed down to a query pulling user account information. In particular, a calculated column was proving both erroneous as well as…
how-oracle-stores-passwords Just minor updates. 18c supports the same hashes as 12cR2 and doesn’t add any new ones. 18c also uses the same sqlnet.ora parameters and values as 12cR2. Updated links to 18c documentation and added note that 18c is the same as 12.1.0.2 and 12cR2.
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data Browsing Attributes Timeouts (this page) Modifying Data Timeouts LDAP servers are designed to be fast; but it is still possible for an action to take a long time. A deep sub-tree search with an open search criteria could require from seconds to even minutes to…
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data Browsing Attributes (this page) Timeouts Modifying Data Searching Hierarchical Data with Attributes Returning attributes with search results The examples in the prior chapter use the “1.1” code to return no attributes. In this example we’ll return two of the attributes associated with an entry. Instead…
Table of Contents Introduction Establishing a Connection Searching Hierarchical Data (this page) Browsing Attributes Timeouts Modifying Data Searching Hierarchical Data Once you’ve established a connection, the next thing you’ll probably want to do is start searching for records. Either to pull the data back to a client, or to modify one or more records or…
Last week the 18c Linux on-premises software was released for download. So this weekend I installed it on my test server and gave it a whirl. The software installs themselves went smoothly and then it was time to do an upgrade. Below I’ve capture the steps and output of an upgrade by unplug/plug from older…
woohoo finally! http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html
I was working on a report from our RMAN catalog and found I was having trouble reconciling the data I found in RC_BACKUP_SET with what I was expecting to see. Furthermore I couldn’t find anything in the Reference Guide to adequately explain what I was seeing. In particular I’ll reference the descriptions of the BACKUP_TYPE…
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…
In Toad version 13.0, after upgrading to an 18c client, the dropdown list on the LDAP tab of the connection window will be empty. The reason is Toad is looking for the 12c ldap client dll in the 18c $ORACLE_HOME/bin directory. Connections will still work; but if you want the list and search functionality you…
Table of Contents Introduction Establishing a Connection (this page) Searching Hierarchical Data Browsing Attributes Timeouts Modifying Data Establishing a Connection The DBMS_LDAP.INIT function is used to connect to an LDAP server. On success, the function returns a session handle of DBMS_LDAP.SESSION type. FUNCTION init (hostname IN VARCHAR2, portnum IN PLS_INTEGER ) RETURN SESSION; DBMS_LDAP.PORT is…
Introduction In 9iR1 (9.0.1) Oracle released the first version of DBMS_LDAP. Between then and 18c the package has received a few enhancements including support for BLOBs, Basic Encoding Rules (BER,) and NLS character set conversions. The documentation for the package has alwasy been somewhat terse, especially if you’re unfamiliar with LDAP structures and usage. I’ve…
Finally getting around to posting these, sorry about the delay. My powerpoint slides can be downloaded here. Some of the slides included simplified queries so the text would fit on a single screen and remain legible. To find who is using temp, a more functionally robust query can be found here: /2018/03/08/who-is-using-temp-and-what-are-they-doing/ Similarly, the who…
This past week I was looking through the Oracle documentation on a task of pedantry; looking up the exact description of Oracle’s outer join syntax. While digging through the syntax diagrams and explanations of the SQL Reference I came across the phrase “partitioned outer join”. It’s an old feature now, having been around since 2003…
Several years ago I made a little math joke about a process that performed millions of look-ups and took forever to complete. That joke, just a short one-liner: “Fast Times Many Equals Slow” has since become a bit of a mantra. The problem can be particularly pernicious in that a problematic set of code might…
I got back from speaking at Collaborate 18 a week ago. My topic was performance diagnostics. The recurring theme of my talk is for developers to leave themselves some bread crumbs to mark their trail. The Oracle database has wonderful diagnostic tools like ASH/AWR, SQL Monitor, tracing, and the hierarchical profiler; but as good as…
I tried the following query on 18c via Oracle’s LiveSQL site and the results don’t correspond to the documented functionality; or, at least, not my reading of the functionality. select to_utc_timestamp_tz(‘2018-04-27’) t from dual union all select to_utc_timestamp_tz(‘2018-04-27T17:40:25+00:00’) from dual union all select to_utc_timestamp_tz(‘2018-04-27T17:40:25Z’) from dual union all select to_utc_timestamp_tz(‘20180427T174025Z’) from dual union all select…
I presented this topic at the Collaborate 2018 conference. My slides can be found here. A couple of the slides included simplified queries so the text would fit on a single screen and remain legible. To find who is using temp, a more functionally robust query can be found here: /2018/03/08/who-is-using-temp-and-what-are-they-doing/ Similarly, the who is…
This is another article inspired by a recent request. That is, given a PDF file with tabular data, could I extract the data into a more human-usable format? Yes, it is possible but it requires multiple steps and does come with some caveats. The first step is converting the PDF into a format that is…
Recently I was asked to look at a problem where a simple query refused to use an index. The table had several million rows in it with a single index on one column populated with 3 values (in my sample the values are A, B, and C.) CREATE TABLE null_test ( col1 NUMBER, col2 NUMBER,…