8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here


Oracle 23c Articles

Oracle Database 23c : Miscellaneous Articles

Oracle 23c will be the next long term release of the database. In preparation, aim to get all your current databases upgraded to Oracle 19c to ease migration to 23c.

Aggregation over INTERVAL Datatypes in Oracle Database 23c - Oracle 23c allows us to use the SUM and AVG aggregate and analytic functions with INTERVAL datatypes for the first time.

Annotations in Oracle Database 23c - This article describes the use of annotations to document our database objects in Oracle database 23c.

ARGUMENT Command in SQL*Plus 23c and SQLcl 22.4 - The ARGUMENT command in SQL*Plus 23c and SQLcl 22.4 allows us to manage arguments passed to a script. This includes the prompt or default value if an argument is missing, and whether the user input from a prompt should he echoed to the screen.

Auditing Enhancements in Oracle Database 23c - This post describes some of the auditing enhancements in Oracle database 23c.

Automatic PL/SQL to SQL Transpiler in Oracle Database 23c - The automatic SQL transpiler in Oracle 23c allows some functions to be converted into SQL expressions to reduce the overhead of function calls in SQL.

Blockchain Table Enhancements in Oracle Database 23c - Blockchain tables were first introduced in Oracle 21c, and backported to Oracle 19c. This article demonstrates the enhancements to blockchain tables in Oracle 23c.

Boolean Data Type in Oracle Database 23c - Oracle database 23c introduced the boolean data type in SQL.

CASE Statement and CASE Expression Enhancements in Oracle Database 23c - In Oracle database 23c the simple CASE statement and expression are more flexible, allowing dangling predicates and multiple choices in a single WHEN clause.

CEIL and FLOOR Support DATE, TIMESTAMP, and INTERVAL Data Types in Oracle Database 23c - In Oracle database 23c the CEIL and FLOOR functions support the DATE, TIMESTAMP and INTERVAL data types.

Database Resident Connection Pool (DRCP) Enhancements in Oracle Database 23c - Oracle database 23c enhanced the Database Resident Connection Pool (DRCP) functionality to include multiple named pools and implicit connection pooling.

DB_DEVELOPER_ROLE Role in Oracle Database 23c - Oracle database 23c introduced a new DB_DEVELOPER_ROLE role, to provide the basic roles and privileges Oracle believe are necessary for a database developer.

DBMS_HCHECK in Oracle Database 23c - In Oracle 23c the DBMS_HCHECK package allows us to check for known data dictionary problems in the database.

DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23c - In Oracle 23c we can define a column as DEFAULT ON NULL FOR INSERT AND UPDATE. This replaces explicit null values with the default value in update statements.

Direct Joins for UPDATE and DELETE Statements in Oracle Database 23c - From Oracle database 23c onward we are allowed to use direct joins to tables to drive UPDATE and DELETE statements.

DML RETURNING Clause Enhancements in Oracle Database 23c - This article demonstrates the enhancements to the DML RETURNING clause introduced in Oracle database 23c.

Domains in Oracle Database 23c - This article demonstrates the use of Domains in Oracle database 23c.

Enhanced Partitioning Metadata in Oracle Database 23c - Oracle database 23c makes it easier to display the high value of partitions using two new columns in the ALL_TAB_PARTITIONS view.

FUZZY_MATCH and PHONIC_ENCODE Data Quality Operators in Oracle Database 23c - Oracle database 23c introduced the FUZZY_MATCH and PHONIC_ENCODE data quality operators to perform fuzzy string matching.

GROUP BY and HAVING Clauses Using Column Aliases in Oracle Database 23c - From Oracle 23c onward we can use the column alias in GROUP BY and HAVING clauses, or the column position in the GROUP BY clause.

Hybrid Partitioned Table Enhancements in Oracle Database 23c - This article demonstrates hybrid partitioned tables with interval and auto-list partitioning in Oracle Database 23c.

Hybrid Read-Only Mode for PDBs in Oracle Database 23c - Oracle 23c allows us to open PDBs in hybrid read-only mode. This allows common users to work in read-write mode, while local users and common application users are restricted to read-only mode.

IF [NOT] EXISTS DDL Clause in Oracle Database 23c - Prevent errors during object creation and removal using the IF [NOT] EXISTS DDL clause in Oracle Database 23c.

Immutable Table Enhancements in Oracle Database 23c - Immutable tables were introduced to Oracle 21.3 and 19.11 at the same time, so it could be considered a 19c and 21c new feature. This article demonstrates the enhancements to immutable tables in Oracle 23c.

JSON_ARRAY Using Subqueries in Oracle Database 23c - From Oracle database 23c onward the JSON_ARRAY function can accept a subquery as input, which brings it in line with the current SQL/JSON standard.

JSON Data Type Constructor Enhancements in Oracle Database 23c - The JSON data type constructor can now accept collections, object types and record types as input.

JSON-Relational Duality Views in Oracle Database 23c - JSON-relational duality views expose our relational data as JSON documents, allowing both query and DML operations to be performed using conventional SQL or directly using JSON.

JSON Schema in Oracle Database 23c - In Oracle database 23c a JSON Schema can validate the structure and contents of JSON documents in your database.

JSON_SERIALIZE : ORDERED Keyword in Oracle Database 23c - In Oracle 23c the ORDERED keyword has been added to the JSON_SERIALIZE function. When present, the members are serialized in ascending alphabetical order by field name.

JSON Support Enhancements in Oracle Database 23c - Oracle 23c includes several enhancements to the JSON functionality in the database. This post acts as a links page to separate articles on each feature.

JSON_TRANSFORM Enhancements in Oracle Database 23c - In Oracle 23c the JSON_TRANSFORM function has lots of new operations, conditional control and richer path support.

JSON_VALUE Function Enhancements in Oracle Database 23c - In Oracle database 23c the RETURNING clause of the JSON_VALUE function has been enhanced allowing it to convert JSON data to user-defined types.

Loading JSON Data using External Tables in Oracle Database 23c - In Oracle database 23c loading JSON data using external tables got much simpler.

Loading SODA Collections with SQL*Loader in Oracle Database 23c - In Oracle 23c we can use SQL*Loader to load JSON data into a Simple Oracle Document Access (SODA) collection.

Lock-Free Reservations to Prevent Blocking Sessions in Oracle Database 23c - In Oracle 23c we can use Lock-Free Reservations to reduce the incidents of blocking sessions for frequently updated numeric column values.

Logical Partition Change Tracking (LPCT) for Materialized Views in Oracle Database 23c - In Oracle database 23c Logical Partition Change Tracking (LPCT) allows materialized view staleness to be tracked at the level of a logical partition.

Materialized View Concurrent Refreshes in Oracle Database 23c - In Oracle 23c materialized view refreshes can be performed concurrently for on-commit fast refreshes by adding the ENABLE CONCURRENT REFRESH clause.

Materialized View Enhancements in Oracle Database 23c - This post introduces some of the materialized view enhancements in Oracle database 23c.

Materialized View Support for ANSI Joins in Oracle Database 23c - In Oracle 23c materialized views support query rewrites for SQL statements using ANSI or Oracle style joins.

MAX_COLUMNS : Increase the Maximum Number of Columns for a Table (Wide Tables) in Oracle Database 23c - Oracle 23c introduced the MAX_COLUMNS initialization parameter, which allows us to have up to 4096 columns in a table.

Migrating Text-Based JSON to JSON Data Type : Pre-Migration Check in Oracle Database 23c - In Oracle 23c the JSON_TYPE_CONVERTIBLE_CHECK procedure has been added to the DBMS_JSON package to allow pre-migration checks to be performed on text-based JSON columns.

Multilingual Engine (MLE) for JavaScript in Oracle Database 23c - Oracle 23c extends the multilingual engine (MLE) with the addition of persistent MLE modules and call specs that allow modules to be called from SQL and PL/SQL.

Oracle REST Data Services (ORDS) : AutoREST of JSON-Relational Duality Views - This article gives an overview of the AutoREST functionality of Oracle REST Data Services (ORDS) against JSON-relational duality views in Oracle 23c.

Oracle SQL and PL/SQL Enhancements in Oracle Database 23c - This page provides links to all the separate articles on SQL and PL/SQL enhancements in Oracle Database 23c.

Precheck Constraints using JSON Schema in Oracle Database 23c - In Oracle database 23c we can use the PRECHECK keyword to mark check constraints as being validated externally by an application.

Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23c - In Oracle database 23c the JSON_QUERY and JSON_VALUE functions can include multiple predicates in a single JSON path expression, and use the PASSING clause to support variables.

Read-Only PDB Users in Oracle Database 23c - Oracle database 23c allows us to make PDB users read-only, which makes a connected session act like the database is opened in read-only mode, preventing the session from performing write operations.

Removal of Touch-Once Restriction after Parallel DML (Unrestricted Direct Loads) in Oracle Database 23c - Oracle database 23c makes parallel DML more flexible by removing the touch-once restriction after parallel DML. This is also know as unrestricted direct loads.

Rename LOB Segments in Oracle Database 23c - The article demonstrates how to rename a LOB segment in Oracle 23c.

Schema Privileges in Oracle Database 23c - Schema privileges allow us to simplify grants where a user or role needs privileges on all objects in a schema.

SELECT Without FROM Clause in Oracle Database 23c - From Oracle 23c onward we can use a SELECT without a FROM clause in some circumstances.

SQL Firewall in Oracle Database 23c - The SQL Firewall allows us to detect, block and log unexpected SQL and session contexts in an Oracle 23c database, giving us greater control to prevent unusual activity.

SQL Property Graphs and SQL/PGQ in Oracle Database 23c - Oracle have had a Graph Server and Client product for some time, but in Oracle database 23c some of the property graph functionality has been built directly into the database.

SQL*Plus New Features in Oracle Database 23c - This article describes the new features of SQL*Plus in Oracle Database 23c.

Staging Tables in Oracle Database 23c - Oracle database 23c introduces the FOR STAGING clause in the CREATE TABLE command to create a variation of heap tables, which have optimal configuration for fast data ingestion.

Table Values Constructor in Oracle Database 23c - The table values constructor allows us to define multiple rows using a single constructor for use in SQL statements.

TLS and Wallet Enhancements in Oracle Database 23c - Oracle 23c includes several enhancements to Transport Layer Security (TLS) and wallets, simplifying configuration, and making the database more secure.

Transport Layer Security (TLS) Connections without a Client Wallet in Oracle Database 23c - In previous releases making HTTPS callouts from the database required the use of a client wallet. From Oracle database 23c onward we can use the operating system certificate store instead.

XML, JSON and Oracle Text Search Index Enhancements in Oracle Database 23c - In Oracle 23c the CREATE SEARCH INDEX statement allows us to create search indexes on XML, JSON and text data, making the syntax consistent between them.

Oracle Database 23c : Installations

Oracle Database 23c Free RPM Installation On Oracle Linux 8 (OL8) - This article describes the RPM installation of Oracle Database 23c Free 64-bit on Oracle Linux 8 (OL8) 64-bit.