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.
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 more easily 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 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.
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.
Multilingual Engine Support for SQL BOOLEAN Data Type
Oracle 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.
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 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 Database.
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.
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.