SQL
Schema Annotations
Schema annotations enable you to store and retrieve metadata about database objects. These are name-value pairs or simply a name. These are free-form text fields applications can use to customize business logic or user interfaces.
Annotations help you use database objects in the same way across all applications. This simplifies development and improves data quality.
Direct Joins for UPDATE and DELETE Statements
Join the target table in UPDATE and DELETE statements to other tables using the FROM clause. These other tables can limit the rows changed or be the source of new values.
Direct joins make it easier to write SQL to change and delete data.
IF [NOT] EXISTS Syntax Support
DDL object creation, modification, and deletion now support the IF EXISTS and IF NOT EXISTS syntax modifiers. This enables you to control whether an error should be raised if a given object exists or does not exist.
The IF [NOT] EXISTS syntax can simplify error handling in scripts and by applications.
New Database Role for Application Developers
The DB_DEVELOPER_ROLE role provides an application developer with all the necessary privileges to design, implement, debug, and deploy applications on Oracle databases.
By using this role, administrators no longer have to guess which privileges may be necessary for application development.
Aggregation over INTERVAL Data Types
You can pass INTERVAL data types to the SUM and AVG aggregate and analytic functions.
This enhancement makes it easier for developers to calculate totals and averages over INTERVAL values.
Automatic PL/SQL to SQL Transpiler
PL/SQL functions within SQL statements are automatically converted (transpiled) into SQL expressions whenever possible.
Transpiling PL/SQL functions into SQL statements can speed up overall execution time.
Client Describe Call Support for Tag Options
Annotations enable you to store and retrieve metadata about database objects. These are either name-value pairs or only a name. These are free-form text fields that applications can use to customize business logic or user interfaces.
Annotations help you to use database objects in the same way, across all applications. This simplifies development and improves data quality.
DEFAULT ON NULL for UPDATE Statements
You can define columns as DEFAULT ON NULL for update operations, which was previously only possible for insert operations. Columns specified as DEFAULT ON NULL are automatically updated to the specific default value when an update operation tries to update a value to NULL.
This feature simplifies application development and removes your need for complex application code or database triggers to achieve the desired behavior. Development productivity is increased and code becomes less error-prone.
DESCRIBE Now Supports Column Annotations
The SQL*Plus DESCRIBE command can now display annotation information for columns that have associated annotations available.
Annotations help you to use database objects in the same way across all applications. This simplifies development and improves data quality.
Data Use Case Domain Metadata Support in OCCI
Provide access to the Data Use Case Domain metadata (domain name and domain schema) for the database columns described in OCCI (Oracle C++ Call Interface) applications.
Database adds Data Use Case Domains to columns and the column metadata need to expose the same in all the data access drivers.
Data Use Case Domains
A data use case domain is a dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints for common values, such as credit card numbers or email addresses. After you define a use case domain, you can define table columns to be associated with that domain, thereby explicitly applying the domain's optional properties and constraints to those columns.
With use case domains, you can define how you intend to use data centrally. They make it easier to ensure you handle values consistently across applications and improve data quality.
Dimension-Wise Arithmetic Support in PL/SQL
Addition (+), subtraction (-), and multiplication (*) can now be applied to vectors in PL/SQL. The arithmetic operation is performed at each dimensional element of the vectors.
Arithmetic operations on vectors allow AI systems to manipulate and combine abstract concepts, enhancing their ability to understand and process language or data in more sophisticated ways. PL/SQL support of vector arithmetic provides developers a means to apply these operations within PL/SQL blocks and functions without calling out to SQL.
Dynamic Statistics for PL/SQL Functions
Dynamic statistics support has been enhanced for PL/SQL functions used in SQL WHERE clauses and TABLE functions. Long-running PL/SQL functions can increase SQL parse times if they are used with dynamic statistics, so global-level and fine-grained controls are provided to configure which functions should be included or excluded.
Cardinality can be difficult to estimate when optimizing SQL statements containing PL/SQL functions. This can lead to poor SQL execution plans and poor SQL performance. The ability to control and use dynamic statistics with PL/SQL functions enables the optimizer to find better execution plans, which leads to improved overall database performance.
Error Message Improvement
The Oracle Call Interface (OCI) OCIError() function has been enhanced to optionally include an Oracle URL with error messages. The URL page has additional information about the Oracle error.
This feature allows users to access information about the cause of the error and the actions that can be taken.
Extended CASE Controls
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.
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.
GROUP BY Column Alias or Position
You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases.
These enhancements make it easier to write GROUP BY and HAVING clauses. It can make SQL queries much more readable and maintainable while providing better SQL code portability.
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 AI Database 26ai, 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 AI Database 26ai), 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.
Improved TNS Error Messages
This feature enhances common TNS error messages by providing more information, such as cause of the error and the corresponding action to troubleshoot it.
Having a better description of errors improves diagnosability.
Materialized View Query Rewrite with Cursor Sharing in the Presence of Bind Variables
This enhancement allows query rewrite to work even when user bind variables are present in the filter predicates. By enabling bind peeking during containment checks, queries that previously required hard parsing can now leverage materialized views more efficiently. This also improves cursor sharing for rewritten queries, reducing parsing overhead and improving performance in systems that rely heavily on bind variables.
This feature removes key limitations in query rewrite and cursor sharing when using bind variables. It enables broader use of materialized views for queries with bind-based filters and allows rewritten queries to share cursors, reducing hard parses and improving overall query performance and response time.
Multilingual Engine Support for SQL BOOLEAN Data Type
Oracle AI Database features a native SQL BOOLEAN data type. The server-side JavaScript engine fully supports the data type on all interfaces.
When using JavaScript to write stored code in Oracle, this feature allows you to take full advantage of the capabilities offered by the new SQL BOOLEAN data type.
Non-Positional INSERT Clause
Oracle AI 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 C++ Call Interface (OCCI) Support for SQL BOOLEAN Data Type
Oracle C++ Call Interface (OCCI) now supports querying and binding of the new SQL BOOLEAN data type.
Using the SQL BOOLEAN data type enables applications to represent state more clearly.
Oracle Client Driver Support for SQL BOOLEAN Data Type
Oracle client drivers support fetching and binding the new BOOLEAN database column.
Applications can use the native database BOOLEAN column data type with a native driver BOOLEAN data type. This enhancement makes working with BOOLEAN data types easier for developers.
SELECT Without FROM Clause
You can now run SELECT expression-only queries without a FROM clause.
This new feature improves SQL code portability and ease of use for developers.
SQL BOOLEAN Data Type
Oracle AI Database now supports the ISO SQL standard-compliant BOOLEAN data type. This enables you to store TRUE and FALSE values in tables and use BOOLEAN expressions in SQL statements.
The BOOLEAN data type standardizes the storage of Yes and No values and makes it easier to migrate to Oracle AI Database.
SQL Time Bucketing
Time bucketing is a common operation when processing time series or event streaming data where a series of data points within an arbitrarily defined time window need to be mapped to a specific fixed time interval (bucket) for aggregated analysis.
With the new SQL operator TIME_BUCKET, Oracle provides native and performant support for time bucketing of time-based data for DATETIMES.
Providing a native SQL operator for common fixed-time interval bucketing for time series data significantly simplifies application development and data analysis of such information. In addition to simpler and less error prone code, the native operator increases the performance of time series analysis.
SQL UPDATE RETURN Clause Enhancements
The RETURNING INTO clause for INSERT, UPDATE, DELETE and MERGE statements are enhanced to report old and new values affected by the respective statement. This allows developers to use the same logic for each of these DML types to obtain values pre- and post-statement execution. Old and new values are valid only for UPDATE statements. INSERT statements do not report old values and DELETE statements do not report new values. MERGE can return both old and new values.
The ability to obtain old and new values affected by INSERT, UPDATE, DELETE and MERGE statements, as part of the SQL command's execution, offers developers a uniform approach to reading these values and reduces the amount of work the database must perform.
SQL*Plus Support for SQL BOOLEAN Data Type
SQL*Plus supports the new SQL BOOLEAN data type in SQL statements and the DESCRIBE command. Enhancements to the COLUMN and VARIABLE command syntax have also been made.
SQL*Plus scripts can take advantage of the new SQL BOOLEAN data type for easy development.
Support for the QUALIFY Clause
The QUALIFY clause filters the results of analytic functions in a SELECT statement. The relationship between the QUALIFY clause and an analytic function is analogous to the relationship between the HAVING and GROUP BY clauses.
Without the QUALIFY clause, the only way to filter on the output of an analytic function is by nesting the query and applying a filter in the parent query, which is more verbose, adds additional maintenance overhead and may obscure the query's intent to the reader. The QUALIFY clause overcomes all these caveats.
Table Value Constructor
The database's SQL engine now supports a VALUES clause for many types of statements. This new clause allows for materializing rows of data on the fly by specifying them using the new syntax without relying on existing tables. Oracle supports the VALUES clause for the SELECT, INSERT, and MERGE statements.
The introduction of the new VALUES clause allows developers to write less code for ad-hoc SQL commands, leading to better readability with less effort.