Direct Joins for UPDATE and DELETE in Oracle Database 23ai / 26ai

One of the most elegant SQL enhancements in Oracle Database 23ai (continued in 26ai) is the support for direct joins in UPDATE and DELETE statements. This syntax lets you perform DML that references other tables using a FROM clause—without resorting to nested subqueries or EXISTS.

The new syntax

UPDATE with direct join

UPDATE target_table alias
   SET column = expression [, column = expression, ...]
   FROM join_table jt1 [JOIN jt2 ON ...]
  WHERE join_condition;

DELETE with direct join

DELETE target_table alias
  FROM join_table jt1 [JOIN jt2 ON ...]
 WHERE join_condition;

Oracle determines the target of the DML by the table that follows the UPDATE or DELETE keyword.

Continue reading →

Oracle 23c/23ai: DEFAULT ON NULL in Action

Understanding Legacy, Insert-Only, and Insert-and-Update Defaults

Oracle 23c introduced a subtle but powerful enhancement to column defaults: the ability to decide when a default value should automatically replace a NULL.

Until now, the DEFAULT clause worked only when the column was omitted in an INSERT. With 23c, we can tell Oracle to also apply defaults when you explicitly insert NULL, and even when you update a column to NULL.

Continue reading →

This article presents how to install Oracle 26AI Release on Oracle Enterprise Linux 9 (OEL9) using RPM.

This article presents how to install Oracle 23AI Release on Oracle Enterprise Linux 9 (OEL9) using RPM.

Continue reading →

This article presents how to install Oracle Enterprise Linux 9.

I assume you have already downloaded Oracle Enterprise Linux 9 64 bit(about 4 G) and you know how to use VirtualBox 64 bit(100M). Create virtual machine with default settings for Oracle Linux 64 bit. You can set 8GB for future Oracle database software installation and 64G for disk. Rest of options you can keep default.

Continue reading →

To avoid errors and extra errors handling in script or PL/SQL code Oracle extended CREATE/DROP DDL with IF[NOT]EXISTS clause

CREATE TABLE IF NOT EXISTS test_tbl
(
id1 NUMBER
);

DROP TABLE IF EXISTS test_tbl;

it’s real game changer for coding. Life is more simple now.

Continue reading →

This article describes nice new feature SQL_MACRO that allows to simplify coding in PL/SQL for table expressions.

Continue reading →

This article shows new columns HIGH_VALUE_CLOB type CLOB, HIGH_VALUE_JSON type JSON in dictionary views DBA|ALL|USER_TAB_PARTITIONS

Continue reading →

This article presents how to install Oracle 23C developer Release on Oracle Enterprise Linux 8 (OEL8) using RPM.

Continue reading →

Oracle 21c introduces a new feature called Blockchain Tables, which allows users to store and query data stored on a blockchain network directly in the database. This feature makes it easy to integrate blockchain data into Oracle-based applications, and enables users to leverage the security and immutability of blockchain technology while still using familiar SQL commands to access and manipulate the data.

Continue reading →