Oracle 23ai New Features: RETURNING Clause & IF EXISTS Explained

Introduction

Modern database development is all about efficiency, simplicity, and control. As applications grow, developers need smarter ways to handle data operations without writing complex code or dealing with unnecessary errors.

That’s exactly what Oracle Database 23ai brings to the table.

Two powerful enhancements introduced are:

• The RETURNING clause for UPDATE and MERGE
• The IF EXISTS / IF NOT EXISTS operators

These features help developers:

• Reduce boilerplate code
• Improve performance
• Handle data changes more intelligently

In this blog, we’ll break down these features with practical examples and explain why they matter in real-world scenarios.

---

Understanding the RETURNING Clause

The RETURNING clause is not entirely new in Oracle—but in 23ai, it has been enhanced to work more effectively with:

• INSERT
• UPDATE
• DELETE
• MERGE

What Does It Do?

The RETURNING clause allows you to:

✔ Retrieve data affected by a DML operation
✔ Capture values immediately after execution
✔ Access old and new values (in certain cases)
✔ Perform calculations on updated data

This eliminates the need for additional SELECT queries after a DML operation.

---

Why This Matters

Traditionally, if you updated a row and wanted to see what changed, you had to:

1. Run an UPDATE
2. Run a SELECT query
3. Compare results manually

Now, with RETURNING:

👉 Everything happens in one statement

---

Example: Capturing Old Salary Before Update

Let’s say you want to double an employee’s salary—but also store the previous salary.

Step 1: Define Variables

VARIABLE l_id NUMBER;
VARIABLE l_old_sal NUMBER;BEGIN
  :l_id := 100;
END;
/

---

Step 2: Update with RETURNING

UPDATE employees
SET salary = salary * 2
WHERE employee_id = :l_id
RETURNING salary/2 INTO :l_old_sal;

---

Step 3: View Result

PRINT l_old_sal;

Result

You get the original salary before the update.

---

Key Behavior of RETURNING Clause

Understanding how it behaves with different DML operations is important:

Operation	Old Values	New Values
INSERT	❌ Not Available	✔ Available
UPDATE	✔ Available	✔ Available
DELETE	✔ Available	❌ Not Available
MERGE	✔ Available	✔ Available

Important Notes

• Only UPDATE and MERGE support both old and new values
• INSERT does not have “old” data
• DELETE does not produce “new” data

---

RETURNING with MERGE Statement

The MERGE statement is commonly used for:

• Upserts (insert or update)
• Data synchronization
• ETL pipelines

With Oracle 23ai enhancements, you can now:

✔ Capture both old and new values
✔ Track changes in a single operation
✔ Improve auditing and debugging

---

Real-World Use Cases

1. Auditing Changes

Track previous and new values for compliance.

2. Logging Updates

Store old values before modification.

3. Performance Optimization

Avoid extra SELECT queries.

4. Real-Time Data Processing

React instantly to changes in applications.

---

Introducing IF EXISTS and IF NOT EXISTS

Another powerful feature in Oracle 23ai is:

👉 IF EXISTS / IF NOT EXISTS

These operators help you avoid unnecessary errors when working with database objects.

---

Problem Before This Feature

Previously:

• Creating a table that already exists → ❌ Error
• Dropping a table that doesn’t exist → ❌ Error

Developers had to write:

• Exception handling
• Conditional checks
• Extra PL/SQL blocks

---

Solution: IF EXISTS / IF NOT EXISTS

Now, Oracle simplifies this with clean syntax.

---

Example: Create Table IF NOT EXISTS

CREATE TABLE IF NOT EXISTS demo (
  id NUMBER,
  empname VARCHAR2(100)
);

What Happens?

• If table does not exist → ✅ Created
• If table exists → ✅ No error, success message

---

Example: Drop Table IF EXISTS

DROP TABLE IF EXISTS demo;

What Happens?

• If table exists → ✅ Dropped
• If table does not exist → ✅ No error

---

Why This Feature Is Important

1. Cleaner Code

No need for:

• Try-catch blocks
• Exception handling
• Manual existence checks

---

2. Better Developer Experience

Developers can focus on logic instead of handling errors.

---

3. Cross-Platform Compatibility

Other databases (like MySQL, PostgreSQL) already support this.

Now Oracle aligns with modern standards.

---

4. Automation-Friendly

Perfect for:

• Deployment scripts
• CI/CD pipelines
• DevOps workflows

---

Combining Both Features in Real Projects

Imagine this scenario:

• You deploy a script
• Ensure table exists
• Perform update
• Capture old values

With Oracle 23ai, you can:

✔ Avoid errors using IF EXISTS
✔ Capture changes using RETURNING
✔ Reduce multiple queries into one

---

Best Practices

✔ Use RETURNING for Performance

Avoid unnecessary SELECT queries.

✔ Use IF EXISTS in Scripts

Especially in automated deployments.

✔ Validate Logic Carefully

Ensure correct columns are captured.

✔ Combine with Logging

Store returned values for audit trails.

---

Common Mistakes to Avoid

• ❌ Expecting old values from INSERT
• ❌ Expecting new values from DELETE
• ❌ Forgetting RETURNING works per row
• ❌ Misusing IF EXISTS in unsupported versions

---

Final Thoughts

Oracle 23ai continues to evolve toward:

• Simpler syntax
• Better performance
• Developer-friendly features

The RETURNING clause enhancements and IF EXISTS operators are small changes—but they bring huge productivity gains.

---

Conclusion

If you’re working with Oracle databases, these features can:

• Save development time
• Reduce complexity
• Improve code quality

Whether you’re a DBA, developer, or data engineer—mastering these tools will give you a clear advantage.

---

Final Tip

Start using these features in your daily SQL scripts.

Because sometimes, the smallest improvements lead to the biggest impact.