Oracle has supported the RETURNING clause in DML for years, but Oracle Database 23ai / 26ai significantly improves its usability by allowing direct access to OLD and NEW column values in INSERT UPDATE DELETE MERGE statements.

This makes it much easier to capture before-and-after data without triggers or extra queries.

Continue reading →

Oracle Database 23ai (and 26ai) introduces table value constructors using the VALUES clause.
This lets you define inline row sets directly in SQL—no DUAL, no UNION ALL, no temporary tables.

You can use VALUES in SELECT, INSERT, UPDATE, MERGE, and WITH clauses.

Continue reading →

If you’ve ever written analytics queries with RANK(), DENSE_RANK(), ROW_NUMBER(), AVG() OVER(...), etc., you know the pain:

• you compute a window value in the SELECT
• then you can’t reference it in WHERE
• so you wrap everything in a subquery… just to filter on that computed value

Oracle Database 26ai fixes this nicely with QUALIFY.

SELECT
  ...
  RANK() OVER (ORDER BY revenue DESC) AS rev_rank
FROM test_tbl
QUALIFY rev_rank <= 5
ORDER BY rev_rank, name;

QUALIFY is evaluated after window functions are computed, so you can filter on them directly — no nesting needed.

Continue reading →

Oracle Database 23ai introduces GROUP BY ALL, a small SQL feature that removes the need to manually list columns in the GROUP BY clause.

Continue reading →

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 →