2024-06-15

IslandSQL Episode 8: What’s New in Oracle Database 23.4?

Introduction In the last episode, we looked at some features in PostgreSQL which I miss in the Oracle Database. The IslandSQL grammar now covers PL/SQL and the related DDL statements. The implementation was more complex than expected, mainly because of the incompatibilities between PostgreSQL and the Oracle Database. I will probably deal […]
2024-03-23

IslandSQL Episode 7: DML Statements in PostgreSQL 16 and What I Miss in Oracle Database 23c

Introduction In the last episode, we covered DML statements in SQL*Plus/SQLcl scripts for the Oracle Database 23c. The IslandSQL grammar can now also handle PostgreSQL 16 DML statements in psql scripts. In this blog post, we will look at some features in PostgreSQL 16 which I miss in the Oracle Database 23c. […]
2024-01-30

IslandSQL Episode 6: DML Statements in Oracle Database 23c

Introduction The IslandSQL grammar now covers all DML statements. This means call, delete, explain plan, insert, lock table, merge, select and update. In this episode, we will focus on new features in the Oracle Database 23c that can be used in insert, update, delete and merge statements. For the select statement see […]
2024-01-12

IslandSQL Episode 5: Select in Oracle Database 23c

Introduction In the last episode, we extended the expressions in the IslandSQL grammar to complete the lock table statement. The grammar now fully covers expressions, conditions and the select statement. In this episode, we will focus on optimizer hints and new features in the Oracle Database 23c that can be used in the […]
2023-12-18

Autonomous Transactions

Introduction Autonomous transactions became available in the Oracle Database 8i Release 1 (8.1.5). 25 years ago. Before then the feature was used only internally, for example, when requesting a new value from a sequence. I mean, if Oracle is using autonomous transactions internally and they’ve made them public, then the usage can […]
2023-03-05

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 that. In this episode, we will have […]
2023-02-19

IslandSQL Episode 3: Lock Table

Introduction In the last episode, we extended the IslandSQL grammar to cover all DML statements as a 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 the foundation for the other DML […]
2023-02-07

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 binaries on Maven Central. Lexer Changes The […]
2023-02-04

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 iteration, we focus on the select statement. Everything […]
2022-10-30

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 a different value. In 19c true and […]