New “Sales History” Sample Schema in Oracle 23c
The newest version of the Oracle sample schema “Sales History” (SH) finally contains current data again and is easier to install than the previous versions. If you work with star schemas, the SH schema is a must. Last week, I had the pleasure to teach…
Enhanced Partitioning Metadata in Oracle 23c
Oracle 23c has a lot of small, but useful enhancements that makes life of developers easier. For example two new columns in the data dictionary views for partition metadata. The description of the LONG data type in the SQL Language Reference of the Oracle documentation…
GROUP BY Extensions in Oracle 23c
Since a few days, Oracle Database 23c Free Developer Release is available. It contains many new features that are interesting for developers, especially some nice little extensions in SQL that we missed since years. Let’s start with an easy one in this blog post. In previous…
IslandSQL Episode 4: Expressions
Introduction In the last episode, we extended the IslandSQL grammar covering the complete lock table statement. However, the support for expressions was very limited. It was not possible to use a date literal or to_date function to determine a partition to be locked. Time to fix…
IslandSQL Episode 4: Expressions
Introduction In the last episode, we extended the IslandSQL grammar covering the complete lock table statement. However, the support for expressions was very limited. It was not possible to use a date literal or to_date function to determine a partition to be locked. Time to fix…
IslandSQL Episode 3: Lock Table
Introduction In the last episode we extended the IslandSQL grammar to cover all DML statements as single lexer token. Now it’s time to handle the complete grammar for one DML statement. The simplest one is lock table. A good reason to start with it and lay…
IslandSQL Episode 2: All DML Statements
Introduction In the last episode we build the initial version of IslandSQL. An Island grammar for SQL scripts covering select statements. In this blog post we extend the grammar to handle the remaining DML statements. The full source code is available on GitHub and the…
IslandSQL Episode 1: Select Statement
Introduction An island grammar focuses only on a small part of a grammar. The island represents the small, interesting part and the sea the rest. In this blog post, I explain the components of an island grammar for SQL scripts named IslandSQL. In the first…
optimizer_secure_view_merging and plsql_declarations
The Original Problem A customer is currently upgrading some Oracle databases from 11.2 to 19c. One query was extremely slow on the new test system and my job was to find out why. The root cause was that the database parameter optimizer_secure_view_merging was set to…
Quoted Identifiers #JoelKallmanDay
Background and TL;DR Connor McDonald wrote a blog post named Cleaner DDL than DBMS_METADATA. Back then he asked me if it would be possible to let the formatter remove unnecessary double quotes from quoted identifiers. Yes, of course. Actually, the current version of the PL/SQL…