Explore Oracle Database 26ai: Interactive SQL with Embedded FreeSQL

-

🚩Introduction 

  • Oracle continues to evolve its database platform with the release of Oracle Database 26ai, introducing several new SQL features for modern data needs. These enhancements focus on improving developer productivity and simplifying everyday query writing. It helps developers work more efficiently while handling growing and complex data workloads.

  • In this blog, we are going to explore few SQL features introduced in the Oracle 26ai. I have embedded FreeSQL terminal in this blog, so the learners can do the hands-on straightaway in this blog along with learning.

📑Why SQL Features in Oracle 26ai Matter

Modern applications demand faster insights, smarter data processing, and seamless integration with AI capabilities. To support these evolving needs, Oracle Database 26ai introduces several new SQL enhancements that make working with data more efficient and flexible. 

  • Improved Developer Productivity

  • Support for Modern Data and AI Workloads

  • Better Query Performance

  • Simplified Data Analysis

  • Stronger Integration with AI Capabilities

👉 Key SQL Features in Oracle 26ai

These SQL improvements help developers write cleaner queries, process complex data types, and build intelligent applications directly within the database. In this blog, we will be covering Five useful SQL enhancements that can make everyday database operations easier and more efficient.

1) Usage of  Exists / Not Exists in DDL statement: 

When creating the tables, you get an error "name is already used by an existing object", this is because, we cannot create the dabase object with similar names. To avoid this errors, they introduced new syntax for creating the tables in oracle 26ai - Exists / Not Exists.

Using this, the developers can create the table only if the table does not exists. It will not throw the error. It will check whether the table exists in the database, if it then it will automatically omits the execution.

/* Not Exists in DDL */

Create table if not exists employee(
    employee_id number primary key, 
    employee_name varchar2(65),
    designation varchar2(45)
);

When you are dropping the table, if the table does not exist, then you will get "table or view does not exist". To prevent this, you can use Exists in your drop statement, this will execute the statement only when table present in the database.
/* Exists in DDL */

Drop table if exists employee;

You can straightaway practice these SQL commands in the below terminal. IF you are executing any DDL / DML operations it will ask to sign in.

2) Inserting Multiple Values using a single Insert statement.

When you need to insert multiple records into your table manually, you need to write mutiple insert statements seperately and you need to execute it. But in oracle 26ai they introduced special syntax where you write one insert statement and add all values within that.
/* Inserting Multple Values */

insert into temp_tbl values (111, 'John','Senior Engineer',45000,1),
                            (112, 'Alex','Cloud Engineer',65000,1),
                            (113, 'Charles','UI/UX',35000,2);

 3) Usage of Boolean data type in the table column: 

In the earlier versions, you will not have the option for using the boolean database for your column within the table. But in Oracle 26ai, you have the provision for the creating boolen columns.
/* Boolean Datatype */

Create table if not exists employee(
    employee_id number primary key, 
    employee_name varchar2(65),
    designation varchar2(45),
    is_active  boolean
);

4) Using column position in the Group by Statement: 

We all used the Group by expresions in our sql statement, like getting department-wise avg salary or the state-wise total sales. when using these statements, we need to mention that column name in the group by statement. In oracle 26ai we can also include the column position in the group by statement instead of the column  name. To achieve this you need to enable the group by position in the session. 
/* Group by Position */

ALTER SESSION SET group_by_position_enabled = TRUE;



select job_id, sum(salary)
  from hr.employees
group by 1 --- Position

5) Using Alias name in the order by Statement :

We all used the order by statement, where we will be sorting our results either in ascending or descending order. when using these statements, we need to mention that column name in the order by statement. In oracle 26ai we can also include the column position in the order by statement instead of the column  name. 
/* Using Alias name */


select first_name || ' - ' || last_name full_name
  from hr.employees
group by full_name --- column alias


🔥 Conclusion

Thus, exploring the new SQL features of Oracle Database 26ai in the interactive way by embedding the FreeSQL terminal allows developers to learn and practice along the way. These SQL enhancements simplify query writing, improve performance, and enable smarter, data-driven applications, making daily database operations more efficient and developer-friendly.


Comments

Post a Comment

Popular posts from this blog

🔍 Extending Smart Search Filter for Multiple Regions

-
Image
🚩Introduction  In Oracle APEX, the Smart Search filter is a convenient feature that allows users to quickly search data in a Classic Report or Cards region without writing custom code. However, this functionality is limited to a single region , which can be restrictive when a page contains multiple regions that should respond to the same search input. In this tutorial, you will learn how to extend the Smart Search filter to work with multiple regions on the same page . We will implement a solution that allows one Smart Search input to drive filtering across multiple Classic Report ,Cards regions etc. By the end of this tutorial, you’ll have a clear, reusable approach for coordinating search behavior across regions, improving both usability and consistency in your Oracle APEX applications without breaking the declarative nature of APEX. 📑 Why Extending the Smart Search Filter Is Needed The built-in Smart Search filter in Oracle APEX works well when a page contains a single sear...
Read more

💡 Designing Dynamic QuickPicks in Oracle APEX

-
Image
🚩Introduction  Oracle APEX provides Quick Picks as a convenient way to allow users to populate page items with predefined values using a single click. While static Quick Picks are useful, real-world applications often require these values to be generated dynamically based on business rules, user roles, or underlying data. This blog explores how to implement Dynamic Quick Picks in Oracle APEX page items , enabling developers to build more intelligent, flexible, and user-friendly forms by driving Quick Pick values directly from database queries and runtime logic.  📑 Why Dynamic Quick Picks Are Needed   Static Quick Picks in Oracle APEX are useful for simple filtering, but Dynamic Quick Picks become essential when: Filter values must be generated at runtime based on current data, user role, or context Applications require data-driven filters instead of hardcoded values The same Quick Picks must adapt across multiple pages or regions Filter options depend ...
Read more

📌Track Active Tab switch in Region Display Selector without any plugins

-
Image
🚩Introduction  In Oracle APEX applications, Region Display Selector components helps to organize multiple regions efficiently. However, APEX does not provide a built-in declarative option to detect which tab is active when users switch between them, making it challenging to trigger conditional logic based on tab selection. This can be solved by creating the custom dyanamic action to capture the tab change. This approach enables dynamic actions like refreshing regions or executing custom logic whenever a tab becomes active, without requiring any plugins. 📑Why It is Essential to Track Tab Selection in Region Display Selector In APEX applications, pages often organize multiple regions under a Region Display Selector to separate content like Details, History, or Reports. While this improves layout, users actions on different tabs often require specific logic to run, such as refreshing a region, loading dynamic content, or updating page items. Without capturing which tab is active, de...
Read more