Nobody knows what kind of restrictions we will experience later this year. That’s why the DOAG Conference + Exhibition 2022 took place in September instead of November. The organizers wanted an in-person event. While you can access some chosen content remotely an in-person event has a lot of advantages, like getting in touch with speakers and attendees. I enjoyed it very much. Many thanks to DOAG and all the people behind the scenes who made this possible.
In this blog post, I summarize some of my personal highlights in chronological order.
Day 1 – Theme Day
The very first day of the conference was a theme day. Themes were
- PL/SQL & APEX
- Oracle Forms
- Database Migrations
- Multitenant Architecture
- Autonomous Database for DevOps
AFAIK all sessions were in German on this day. Participants and speakers without the knowledge of German probably felt a bit lost and took the opportunity to talk over a drink or a meal.
PostgreSQL Features Oracle People Will Like
A highlight of this day was this talk by Hans-Jürgen Schönig. He is Austrian, a Viennese to be exact. I can imagine him as a stand-up comedian. Rarely have I laughed so hard at a technical presentation. He presented some features where PostgreSQL shines and the Oracle database does not look so good. The intention was clear. He wanted to make Oracle database fans jealous. If you have the chance to see Hans-Jürgen live, don’t miss it.
Day 2 and 3 – The Conference
The second day was the start of the real conference. 20 parallel streams with 4 to 6 English talks per time slot. The first session was at 08:00 and the last one at 17:00. A 15-minute break between the 45-minute sessions. There were for sure more attendees than at the theme day, but less than 2019 – the last in-person DOAG conference. And some exhibitors were missing. It hurts to see Robotron at the spot where the Trivadis booth was the previous years. Let’s hope for more exhibitors next year.
These are my highlights of the main conference.
Design Patterns in PL/SQL & SQL
Oren Nakdimon had the bad luck to get the 08:00 slot. However, I was lucky to meet him in the bus to the conference center. He convinced me to attend his session (of course he was already on the short list). He used a data model based on artists, genres, albums and songs for all examples and presented the following patterns:
|Overlap check||Find rows that (partially or fully) overlap a given range.||Find all artists that were living between two dates.|
|Extendable LOV||Look-up a reference table and add new values if not exist.||Get genre ID by its name. If it doesn't exist, add it.|
|Multi-value parameters||Pass multiple entities to procedures||Add an album with its songs.|
|The guardian trigger||Whenever some DML is done on some table T, another piece of code should be executed.||Enforce the rule: an album must have at least one song.|
|Conditional uniqueness||Enforce a business rule that requires uniqueness of an attribute (column) within a subset of the entity instances (table rows).||Enforce the rule: each album may have at most one favourite song.|
|Correlated results||Return correlated data based on various inputs.||Return albums – and all their songs – based on various inputs.|
|Arc relationship||Implement an arc relationship from a generic entity.||Implement an arc relationship from images to artists and albums.|
For each pattern Oren explained the problem and build the suggested solution step by step. He considered also various corner cases.
Have a look at his website where you find the slides (search for “Design Patterns in PL/SQL and SQL”). I’m sure that some of us have solved one or the other problem in a less elegant way.
Oracle Analytics Server, Make It Right from the Beginning
Gianni Ceresa‘s talk was about installing the Oracle Analytics Server the right way. However, a lot of his talk was independent of the product. Simply put it was about reading the documentation, automation and testing. “Script as much as you can” was one of Gianni’s messages. There was also an interesting side story about how a cloud provider can mess up provisioning its own product. See this blog post to learn more.
The Future of the Oracle Database
The Oracle Database version 23c is the next long-term release. The release is expected in April 2023. The beta program will start in October and ends in February 2023. Gerald Venzl revealed in this session some new features. He presented the following features with some examples:
- Schema Level Privileges
- SQL Domains
- 4096 Columns
- JSON Schema
- Boolean Data Type
- UPDATE via JOIN
- IF [NOT] EXISTS
- GROUP BY Alias / Column Position
- Table Value Constructor
- Better RETURNING CLAUSE
- Developer Role
- SELECT without FROM
- Much Better Error Messages
See this Twitter thread for some pictures per feature. More details will probably be available after CloudWorld. I’m really looking forward to get access to this release.
TAPI vs. XAPI
Jürgen Sieben explained based on a simple example the problems of accessing tables directly and using row-level triggers to apply business logic. He improved the situation by using a table API. The main disadvantage of a table API is, that it exposes the implementation such as the structure and the column names. A solution is to use a view layer for read-only access and a transaction API for write access. The table API can still be used, but only internally by the transaction API itself. Finally Jürgen explains that a CRUD based API is a technical interface that obscures the intention. To cancel an order you could delete the order with its positions. But most probably you would want to store the reason of the cancellation and change the state of the order. So, a transactional API should be designed from a business perspective.
I liked this talk very much. Not only because I totally agree with Jürgen, but also because I like the way how he explains things in a concise and easy to follow way.
I enjoyed this conference very much. Thanks to everyone who helped make this conference happen. I’m already looking forward to DOAG2023.