Release Update 23.9 Features
Compile-Time Syntax Checking for JavaScript Procedures and Functions
Syntax checks are now run at compile time on JavaScript functions executed using inline call specifications, providing valuable analysis of your code before runtime.
When used in combination with the linting tool of your choice, compile-time syntax checks can help increase confidence in the readiness of your code by providing an extra step of code analysis. This compile-time support matches the checks already available when creating MLE modules.
EXECUTE Privilege on JAVASCRIPT Not Required Anymore
The EXECUTE ON JAVASCRIPT privilege is no longer required in order to run JavaScript code from your user account.
You can now create Multilingual Engine (MLE) modules and execute inline call specifications to publish JavaScript functions without the extra step of granting the EXECUTE ON JAVASCRIPT privilege. This provides a more streamlined introductory experience to using MLE.
GROUP BY ALL
In SQL queries with complex SELECT lists that contain aggregation functions, the new GROUP BY ALL clause eliminates the need to put all non-aggregated columns into the GROUP BY clause. Instead, the new ALL keyword indicates that the results should be automatically grouped by all non-aggregated columns.
Not having to repeat the non-aggregated columns in the GROUP BY clause, makes writing SQL queries quicker and less error prone.Users can use the GROUP BY ALL functionality to either quickly prototype their SQL query or for quick ad-hoc queries.
Generate and Test UUID Using SQL
UUID is a 128-bit universal unique identifier popularly used by applications to generate an unpredictable, random value which can be used as a primary key in a table, a transaction ID, or any form of unique identifier. In Oracle Database 23ai, SQL function UUID() generates a version 4 variant 1 UUID in the database per the UUID RFC 9562.
The UUID generation and manipulation functions offer a compliant way of generating a random, unique, and unpredictable identifier that can be used to populate a primary key column in a database table, to uniquely identify a transaction ID (for example, for the Sessionless Transactions feature in Oracle Database 23ai), and many other purposes.
Modern applications expect to be able to generate a UUID that is unpredictable and random. All major databases and data management systems support some form of UUID generation and manipulation.
The current Oracle SQL operator SYS_GUID() always produces a predictable sequence of unique identifies which is not optimal.
IVF Index Online Reorganization
The quality of an IVF index may degrade over time if updates to the base table alter the general vector distribution. It is now possible to reorganize an IVF index while it remains available for DMLs and Queries.
IVF indexes can become unbalanced if the source table changes significantly from when the index was originally created. This can potentially impact the performance and quality of the index. With IVF Index online reorganization, it is possible to reorganize the structure of the index while the index remains online and available for DMLs and queries.
JSON to Duality Migrator: Schema Inference using JSON Schema
This feature allows the JSON to Duality Migrator to use user provided JSON schemas for relational schema inference. The JSON schemas can be provided in isolation or in conjunction with data for more accurate schema inference.
This feature makes the JSON to Duality Migrator more accessible to customers who may not have a large amount of JSON data for the Migrator to analyze or who do not want to send confidential data to the Migrator. As long as they have a JSON schema for each collection, they can still use the Migrator.
Multi-Factor Authentication for Database Authentication
You can enhance Oracle Database security by enabling multi-factor authentication for users. You can use push notifications through the Cisco Duo or Oracle Mobile Authenticator app or combine a username and password with PKI (Public Key Infrastructure) certificate-based authentication.
Multi-factor authentication is a required security requirement for many databases, especially if the database holds sensitive data.
Multilingual Engine Supports Database-Resident Connection Pools
JavaScript in Oracle Database (Multilingual Engine) can now be used in database connections using a Database Resident Connection Pool (DRCP). You can now invoke JavaScript procedures and functions in DRCP sessions, and use the DBMS_MLE API.
Support of DRCP extends access to in-database JavaScript and allows those workloads using in-database JavaScript to leverage the benefits of DRCP, such as improved scalability.
Non-Positional INSERT INTO SET Clause
Oracle Database adds a new INSERT INTO SET clause, which is an easier, self-documenting syntax for INSERT INTO statements. The SET clause for the INSERT INTO statement is the same as the already existing SET clause for UPDATE statements. It also adds a BY NAME clause when inserting a the results of a subquery. This matches source and target columns by their name rather than their position in the INSERT and SELECT lists.
The benefit of the SET clause is that it is immediately clear which value in the INSERT INTO statement belongs to which column, which is not obvious and cumbersome to find out for current INSERT INTO statements with hundreds of columns. Similarly matching the order of hundreds of columns in INSERT and SELECT lists when loading a subquery is awkward. Using the BY NAME clause allows these lists to be in different orders, simplifying the process of writing the statement.
Oracle Update Advisor
Oracle Update Advisor is a software update recommendation framework that provides information to keep software at recommended versions.
Using Oracle Update Advisor simplifies software maintenance. In combination with tools such as the Database Creation Assistant (DBCA) and Fleet Patching and Provisioning (FPP), Oracle Update Advisor provides you with your software status and provides update and maintenance recommendations.
Oracle Update Advisor Support with Oracle FPP
You can leverage Oracle Update Advisor with Oracle Fleet Patching and Provisioning (Oracle FPP). Oracle Update Advisor is a software recommendation framework that analyzes the health of Oracle Database and Oracle Grid Infrastructure, provides patch recommendations, and creates fully functional gold images based on your patch maintenance policy. These gold images are downloaded automatically into the Oracle FPP Gold Image repository.
Using Oracle Fleet Patching and Provisioning in conjunction with Oracle Update Advisor greatly simplifies and streamlines the update experience. You can use the Oracle Update Advisor for both Oracle FPP Local Mode and Oracle FPP Central Server Mode.
Partition Maintenance Operations and Direct Load with Global IVF and HNSW Indexes
Partition Maintenance Operations can now be performed on partitioned tables that have global IVF and HNSW Indexes. These operations can be applied to tables that have been partitioned using various methods, including RANGE, LIST, HASH, and COMPOSITE.
Partition maintenance operations such as adding, dropping, merging, and splitting partitions can be performed on tables with global IVF and HNSW Indexes. One of the key benefits of partitioning is the added flexibility of being able to perform maintenance operations on a subset (or partition) of a table in isolation, without impacting the rows in neighboring partitions. This includes tables with Vectors.