Oracle 23ai: New INSERT Statement Features and Practical Examples (Part 2)

 

Introduction

With Oracle Database 23ai, the humble INSERT statement has received some genuinely useful upgrades. What was once a straightforward data-loading operation now supports more advanced practical use cases, especially for modern applications. Whether you’re working with AI-related data, handling high-volume inserts, or trying to simplify everyday SQL logic, these new features are worth paying attention to. Oracle has clearly focused on reducing complexity while improving performance and flexibility. In this blog, I’ll walk through four notable INSERT enhancements in Oracle 23ai. Each one addresses a real-world problem that developers and DBAs commonly face.

Oracle 23ai: New INSERT Statement Features and Practical Examples (Part 1)

 

Introduction

Oracle Database 23ai introduces a major evolution of the INSERT statement, modernizing one of the most frequently used DML operations in Oracle SQL. These enhancements are designed to make data insertion clearer, safer, and more flexible, especially in environments with wide tables, evolving schemas, and bulk data loads. New capabilities such as INSERT … SET, INSERT … BY NAME, multi-row inserts using the VALUES clause, and support for non-numeric column assignments in SET-based inserts reduce reliance on column order, simplify SQL syntax, and help prevent common data-mapping errors. Together, these features significantly improve code readability and developer productivity while aligning Oracle SQL more closely with modern database standards.

Oracle 23/26ai: Advanced Analytics – Mastering Time Intervals and GROUP BY

Introduction

For many years, writing SQL meant dealing with rules that often felt unnecessary. Developers followed them because that was the only way things worked, even when it made queries longer or harder to read. With Oracle Database 23ai, things are starting to improve. Oracle has added new features that make SQL easier to write and easier to understand. In this blog, I’ll cover three of those improvements: aggregating interval data types, using column aliases in GROUP BY, and grouping by column position. These changes may seem small, but they can make a big difference in everyday SQL work.

Interval Data Type Aggregation

Working with time durations in SQL has always been awkward. Storing an interval was never the problem; the trouble started when you wanted to do something useful with it. Simple questions like “What’s the total time spent?” or “What’s the average duration?” often meant converting intervals into seconds or minutes, running the math, and then converting everything back again. It worked, but it wasn’t pretty, and it definitely wasn’t intuitive.

Oracle Database 23ai finally fixes this. Intervals can now be used directly with aggregate functions like SUM and AVG. This means you can work with durations as durations, without going through manual conversions. The database handles the math for you and returns a proper interval result.

Example:

In this example, we’ll look at a support system that tracks how long each ticket takes to resolve. The resolution time is stored using the INTERVAL DAY TO SECOND data type, which is a very natural way to represent durations.

First, we create a table and insert a few sample tickets with different resolution times:

-- Setup: Create a table with interval data
CREATE TABLE support_tickets (
    ticket_id NUMBER,
    resolution_time INTERVAL DAY TO SECOND
);

INSERT INTO support_tickets VALUES (1, INTERVAL '0 02:30:00' DAY TO SECOND);
INSERT INTO support_tickets VALUES (2, INTERVAL '0 01:15:00' DAY TO SECOND);
INSERT INTO support_tickets VALUES (3, INTERVAL '1 04:45:00' DAY TO SECOND);

Each row represents how long a ticket took to close, ranging from just over an hour to more than a full day.

Now, using Oracle 23ai, we can calculate the total and average resolution time directly:

-- Oracle 23ai: Direct aggregation
SELECT 
    SUM(resolution_time) AS total_time,
    AVG(resolution_time) AS avg_time
FROM support_tickets;

Before Oracle 23ai, this query would fail because SUM and AVG did not support interval data types. Developers had to convert intervals into numbers, perform the calculation, and then manually format the result back into a readable duration. In 23ai, Oracle handles this natively. The database understands how to add and average intervals and returns the result as a properly formatted INTERVAL DAY TO SECOND.

GROUP BY Alias

Repeating long expressions in the GROUP BY clause has always been one of Oracle SQL’s biggest annoyances. If you used a CASE expression or formatted value in the SELECT, you had to copy it exactly into the GROUP BY.

Oracle 23ai finally fixes this by allowing you to reference column aliases directly. This makes queries shorter, clearer, and much easier to maintain.

Example:

In this example, employees are grouped into salary bands using a CASE expression. Instead of repeating the entire expression in GROUP BY, I simply reuse the alias salary_level.

SELECT 
    CASE WHEN salary > 10000 THEN 'High' ELSE 'Standard' END AS salary_level,
    COUNT(*) AS total_staff
FROM employees
GROUP BY salary_level;

Group by Position

When you write GROUP BY queries, you often end up repeating the same columns or expressions that already appear in the SELECT list. This can get annoying, especially when those expressions are long or hard to read. Oracle Database 23ai makes this easier by allowing you to group by the position of columns instead of typing them again.

This works similarly to ORDER BY 1, 2. Instead of repeating column names or functions, you can simply refer to where they appear in the SELECT list. While it’s still a good idea to use column names in long-term or production code.

Example:

Since this is a new feature, it needs to be turned on for your session. If you don’t enable it, Oracle will treat the numbers as literal values and raise an error.

 -- Step 1: Enable the feature for your current session
ALTER SESSION SET group_by_position_enabled = TRUE;

NOTE: If you didn’t enable it, you will encounter the following error

Now let’s look at a simple sales example. We want to group sales data by region and by year, and then calculate the total sales amount.

-- Step 2: Group sales data using column positions
SELECT 
    region_name, 
    EXTRACT(YEAR FROM sale_date) AS sale_year,
    SUM(amount) AS total_sales
FROM (
    SELECT 'North' AS region_name, DATE '2023-01-01' AS sale_date, 100 AS amount FROM dual UNION ALL
    SELECT 'South' AS region_name, DATE '2023-05-15' AS sale_date, 250 AS amount FROM dual
)
GROUP BY 1, 2;

In this query, GROUP BY 1, 2 tells Oracle to group the results using the first and second columns from the SELECT list. The first column is region_name, and the second column is the year extracted from sale_date.

Conclusion

Oracle 23ai’s SQL enhancements show a welcome shift toward practicality and developer comfort. Being able to aggregate interval data directly, group by aliases, and group by position removes a lot of unnecessary repetition that used to clutter otherwise simple queries. The result isn’t just shorter SQL—it’s SQL that’s easier to read, reason about, and maintain over time. These features make refactoring older queries genuinely worthwhile, not just cosmetic. If you’re working with Oracle 23ai, it’s well worth revisiting existing SQL and taking advantage of these small but meaningful improvements.

Enhancing Efficiency in Oracle 23ai: Using the Automatic SQL Transpiler

 

What is Context Switching between PL/SQL and SQL?

The PL/SQL engine is a virtual machine that resides in memory and processes the PL/SQL m-code instructions. When the PL/SQL engine encounters an SQL statement, a context switch is made to pass the SQL statement to the Oracle server processes. The PL/SQL engine waits for the SQL statement to complete and for the results to be returned before it continues to process subsequent statements in the PL/SQL block. 

In other words, context switching in Oracle databases refers to the overhead incurred when control shifts between the SQL engine and the PL/SQL engine during the execution of mixed SQL and PL/SQL code. The two engines operate in different runtime environments, so when you run SQL statements within PL/SQL code, the database has to 'switch' between these environments. This switching can cause performance degradation.

Automatic PL/SQL to SQL Transpiler

PL/SQL functions within SQL statements are automatically converted (transpiled) into SQL expressions whenever possible.

Key Rotation for SQL Server TDE in an Always On Availability Group Environment

 

Introduction:

Managing data security in SQL Server goes beyond enabling Transparent Data Encryption (TDE)—it also requires proper lifecycle management of encryption keys, especially in high-availability environments. In an Always On Availability Group setup, performing a TDE key rotation isn't as simple as running a command on a standalone instance. It involves coordinating key changes across replicas while maintaining encryption consistency and ensuring minimal downtime.

In this blog, I’ll walk you through how to safely rotate the Database Encryption Key (DEK) and the Certificate protecting it in a SQL Server Always On environment. You'll learn the prerequisites, steps involved, potential pitfalls, and how to verify that your key rotation was successful across all nodes in the availability group.

SQL Server Always On Across Two Data Centers: Manual and Automatic Failover Testing – Part 2

Introduction

In Part 1 (SQL Server Always On Across Two Data Centers with Dual Witnesses and DNS CNAME), I walked through how we designed and implemented a more resilient SQL Server Always On Availability Group architecture across two data centers, using dual file share witnesses and a DNS CNAME to simplify failover and enhance high availability.

Now, in Part 2, we put that setup to the test.

This blog focuses on failover testing—a crucial step in validating any high availability or disaster recovery solution. I’ll simulate real-world failure scenarios, including the complete loss of the primary data center (along with its witness and replica) and the failure of the DR data center. You’ll see how the system handles these events in both manual and automatic failover modes, and how our configuration ensures continuity without manual reconfiguration or data loss.

Whether you're preparing for disaster recovery testing or simply validating your SQL Server HA setup, these test cases and results will help you understand what to expect and how to respond when failure happens.

SQL Server Always On Across Two Data Centers with Dual Witnesses and DNS CNAME – Part 1

Introduction

In our existing SQL Server Always On configuration, we had a two-node setup, with each node hosted in a separate data center, and a single witness located in the secondary (DR) data center. Due to our application's design requirements, the availability group was configured for manual failover.

However, this architecture introduced a critical limitation: when the DR DC went offline, we lost quorum, and the Always On configuration became unusable. As a result, we were forced to disable and delete the availability group, and then recreate it from scratch, including re-adding all databases—an error-prone and time-consuming process.

To overcome this limitation and ensure high availability across both data centers, we implemented a more resilient architecture: two file-share witnesses—one in each data center—combined with a DNS CNAME record to abstract the witness name. This solution provides redundancy for the quorum configuration and allows the availability group to survive the failure of either data center without needing to rebuild the setup.

In these two blogs, I will walk you through how we built a more resilient Always On architecture. In Part 1 (SQL Server Always On Across Two Data centers with Dual Witnesses and DNS CNAME), I’ll explain the setup of dual file share witnesses across two data centers, along with the use of a DNS CNAME to manage quorum effectively. In Part 2 (SQL Server Always On Across Two Datacenters: Manual and Automatic Failover Testing), I’ll demonstrate how we tested failover in both manual and automatic modes—simulating complete failure of either the primary or secondary data center—and show how this architecture ensures availability without needing to rebuild the configuration.