Some new PL/SQL features in Oracle Database 23c

PL/SQL stands for Procedural Language for SQL. PL/SQL is Oracle’s procedural extension for SQL and is is loosely based on Ada (a variant of Pascal developed for the US Deptartment of Defense).  PL/SQL has been available in Oracle Database since version 6 (first released in 1992) while stored PL/SQL procedures/functions/packages/triggers became available with version 7.

Let us have a look at what is new with Oracle Database 23c. There are 5 main areas:

1. The SQL Transpiler

A new init.ora parameters in Oracle Database 23c called SQL_TRANSPILER enables or disables the SQL transpiler (think of a combination of translate and compile). When this parameter is set to ON, the SQL transpiler feature is enabled and PL/SQL functions are automatically transpiled into SQL expressions whenever possible (does not work for all functions), without user intervention. The conversion operation is transparent to users and can improve performance by reducing overhead accrued from switching between the SQL and PL/SQL runtime.

You can change the value with either “alter system” or “alter session” and in RAC different instances can have different values. The default value is OFF and the parameter can be modified on PDB level too.

SQL> alter system set SQL_TRANSPILER=on scope=both;

System altered.

2. The BOOLEAN data type

Another new init.ora parameter called PLSQL_IMPLICIT_CONVERSION_BOOL (false is default) allows/disallows implicit conversions in PL/SQL. PLSQL_IMPLICIT_CONVERSION_BOOL allows or disallows implicit conversions in PL/SQL. Here are the data types that are converted to boolean and vice versa:

Numeric data type to BOOLEAN:

• Non-zero numeric values are implicitly converted to the BOOLEAN value TRUE
• The numeric value of 0 is implicitly converted to the BOOLEAN value FALSE

Character data type to BOOLEAN:

• Character values such as ‘true’, ‘t’, ‘yes’, ‘y’, and ‘on’ (case-insensitive) are implicitly converted to the BOOLEAN value TRUE
• Character values such as ‘false’, ‘f’, ‘no’, ‘n’, and ‘off’ (case-insensitive) are implicitly converted to the BOOLEAN value FALSE

BOOLEAN to numeric data type

• The BOOLEAN value TRUE is implicitly converted to the numeric value 1
• The BOOLEAN value FALSE is implicitly converted to the numeric value 0

BOOLEAN to character data type

The BOOLEAN value TRUE is implicitly converted to the character value ‘TRUE’

The BOOLEAN value FALSE is implicitly converted to the character value ‘FALSE’

When this parameter is set to FALSE, PL/SQL does not support such implicit conversions. Let us see how this works in practice, here is a small table called DBAs and let us add few Oracle ACEs (and few more non-ACEs) into it (in honor of Lionel Messi winning the Golden Ball/Ballon d’Or last night for the 6th time, 30th of October 2023):

Note that regardless of the way we inserted the boolean value for each one the result is either TRUE or FALSE when PLSQL_IMPLICIT_CONVERSION_BOOL is set to TRUE:

3. IF [NOT] EXISTS Syntax Support

In 23c, the clauses IF NOT EXISTS and IF EXISTS are supported by CREATE, ALTER, and DROP DDL statements. They are used to suppress potential errors otherwise raised by the existence or non-existence of a given object, allowing you to write idempotent DDL scripts.

IF NOT EXISTS cannot be used in combination with OR REPLACE in commands using the CREATE DDL statement.

4. Extended CASE Controls

The simple CASE statement is extended in PL/SQL to support the use of dangling predicates and choice lists, allowing for simplified and less redundant code.

Dangling predicates are ordinary expressions with their left operands missing (for example > 275) that can be used as a selector_value either instead of or in combination with any number of literals or expressions. With dangling predicates, more complicated comparisons can be made without requiring a searched CASE statement.

Here is an example of how dangling predicates work:

The CASE statement is extended in PL/SQL to be consistent with the updated definitions of CASE expressions and CASE statements in the SQL:2003 Standard [ISO03a, ISO03b]. Dangling predicates allow tests other than equality to be performed in simple CASE operations. Multiple choices in WHEN clauses allow CASE operations to be written with less duplicated code.

Currently, the dangling predicates IS JSON and IS OF are not supported.

5. JSON Constructor and JSON_VALUE Support of PL/SQL Aggregate Types

The JSON constructor can now accept a PL/SQL aggregate type and return a JSON object or array populated with the aggregate type data. Conversely, the built-in function json_value now supports PL/SQL aggregate types in the RETURNING clause, mapping from JSON to the specified aggregate type.

All PL/SQL record field and collection data element type constraints are honored by json_value, including character max length, integer range checks, and not null constraints.

SQL objects and PL/SQL record type instances, including implicit records created by the %ROWTYPE attribute, are allowed as valid input to the JSON constructor. There is expanded support for user defined types as input streamlines data interchange between PL/SQL applications and languages that support JSON.

Here is a simple example of using the JSON_SERIALIZE function in order to “transform” and “pretty” print a PL/SQL aggregate (we used to call it unofficially a pseudo table in Oracle 7) into JSON:

Here is how the output from the PL/SQL block above looks like:

Source: juliandontcheff.wordpress.com