Oracle Database 21c for Developers

newsletter SPOUG_Oracle db_mayo

Oracle Database 21c is the latest innovation release. With it comes a whole host of developer focussed SQL and PL/SQL enhancements. This session will cover key features, including:

  • Better loops and qualified expression in PL/SQL
  • Scalar SQL macros
  • JSON improvements
  • SQL window clause enhancements

Better loops and qualified expression in PL/SQL

Initializing and iterating through sparse arrays in PL/SQL was fiddly and needed lots of code. The improved loop controls make this task much easier. Using these you can write complex for loops with much less code.

The new syntax also makes it easy to do previously hard tasks: merging arrays and populating an index-by-varchar2 array with SQL.

Scalar SQL macros

PL/SQL functions are great for creating reusable code units. But using these in SQL has a serious drawback: context switches. The overhead from these could make your queries much slower.

This often posed a dilemma: do you write fast, but hard to maintain SQL queries? Or accept slower run times in exchange for easier development?

Scalar SQL macros offer another way. The database resolves these at parse time, so the expression is part of the SQL statement. These give the same performance as pure SQL with the maintenance benefits of PL/SQL functions.

JSON improvements

JSON is a popular data format for flexible data storage. Since the release of, Oracle has built a wealth of functionality to help you process JSON data. Oracle Database 21c improves this further by adding a JSON data type. This stores documents in an optimized format, speeding up your code.

This release includes several functional improvements too, such as JSON_transform. This makes it easy to change parts of a JSON document.

SQL window clause enhancements

Analytic or window functions are a fantastic tool to analyse data across rows. But if you have several of these in one statement you can end up with duplicate partitioning and ordering clauses. The addition of the window clause enables you to specify these in one place, simplifying your queries.

The addition of the groups frame also give more options for defining which rows to include in the calculation.

Join this session to get an overview of how and when you’ll use these features.

Chris Saxon

Oracle Developer Advocate for SQL