Live Handson Environment for Modern Oracle Database Programming

My fellow Oracle ACE Directors and former AMIS-colleagues Patrick Barel and Alex Nuijten have co-authored an impressive book:

The book discusses many of the wonders of SQL and PL/SQL – the well and lesser known things you can (and should) do when programming in and against the Oracle Database. Part 1 – Advanced Basics – looks at features in SQL and PL/SQL that are underused and should be better known – at least by you (yes you, dear reader!). You’ll learn about new join types, pattern matching across rows, Top N pagination (useful in reporting!), qualified expressions, and enhancements to iterators that reduce code complexity and make your logic easier to understand. SQL and PL/SQL keep evolving. Back in 2003 we introduced the Oracle 7Up training at AMIS – for ourselves and the community because we concluded that even though we were working with Oracle 9iR2, we were basically still using it like we used Oracle7. This book brings you up to speed with Oracle Database 19c and a little bit beyond.

Part II covers how and when to invoke PL/SQL from SQL while maintaining performance. You’ll learn about SQL macro functions for creating reusable SQL fragments and polymorphic table functions with return types determined by incoming argument types. A lot of time (or rather pages) is spent discussing JSON: constructing and parsing JSON documents for data interchange with other systems. JSON is the clear winner in data exchange formats at present (the XML of today) and Oracle Database has extensive support for working with JSON as Patrick and Alex demonstrate.

Part III introduces a vast array of built-in functionality that Oracle provides that is just waiting to be used. Edition-based redefinition (one of my favorite mechanisms in Oracle Database) enables zero-downtime application and schema upgrades. Data redaction enables easier compliance with privacy laws and similar regulations by protecting sensitive data from those who have no need to see it. Virtual private databases provide the appearance of giving each user their own database, again helping to secure sensitive data.

Anyone working with Oracle Database as a developer – either writing PL/SQL to run inside the database or crafting SQL to run against the database – would benefit from reading this book. With its many practical examples – explaining and comparing features in a down to earth manner that far surpasses the often cryptic explanations in the Oracle Database documentation.

With the book comes the source code – code from many of the examples in the book and the DDL and DML scripts to set up the database for running these examples against.

I have takes these scripts and combined them with a Gitpod workspace definition I already had prepared for Oracle Database 23c. The combination of these two are available in this GitHub repository: https://github.com/lucasjellema/modern-oracle-database-programming/tree/gitpod-workspace-23c-free.

Why/when/how is that  interesting? Well: by opening this link you can run a Gitpod workspace (for free) that has an Oracle Database 23c Free instance with the f1data schema installed with all database objects and data that form the source code and handson environment for Patrick & Alex’s book. 

You can connect a local SQL Developer environment to the database in the Gitpod workspace. This makes for a very convenient way of working with the valuable examples (and the extensive Formula One based data schema with over 600K records!)

The database schema of the f1data user looks like this:

The data model used for most of the examples in this book was taken from the Ergast motor racing data website (https://ergast.com/mrd/). The structure of the database is shown in an Entity Relationship Diagram and explained in the Database User Guide.

There are 14 tables – for drivers and constructors (teams), circuits and races and seasons, (driver) results (in qualification, sprints and races) and details for laptimes and pitstops. Intermediate standings for drivers and constructors (teams) are represented as well. There is data for the Formula One series, from the beginning of the world championships in 1950. Details on the properties of all data records can be inspected on this website: https://ergast.com/mrd/.

Resources

Publisher’s page on the book   – Modern Oracle Database Programming – Level Up Your Skill Set to Oracle’s Latest and Most Powerful Features in SQL, PL/SQL, and JSON

Source Code repo for Modern Oracle Database Programming: Level Up Your Skill Set to Oracle’s Latest and Most Powerful Features in SQL, PL/SQL, and JSON by Alex Nuijten and Patrick Barel (Apress, 2023): https://github.com/Apress/modern-oracle-database-programming

My fork with the Gitpod Workspace definition: https://github.com/lucasjellema/modern-oracle-database-programming/blob/gitpod-workspace-23c-free/README-gitpod-oracledatabase23c.md