My video demonstration on using python-oracledb to extract data from an Oracle Database and read it into a Pandas Dataframe
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
06 September, 2026
08 August, 2026
New JOIN TO ONE clause in 26.2 SELECT
I've just published a short video on the new JOIN TO ONE clause in SELECT statements in Oracle 26ai 26.2
This clause allows you to let the database automatically determine JOIN columns based on Primary Key and Foreign Key relationships configured in the database. JOIN TO ONE defaults to doing a LEFT OUTER JOIN so I also demonstrate how to use it for INNER JOINs
05 August, 2026
Tracing a Power BI DirectQuery Refresh in Oracle
In today's video I have demonstrated how Power BI can use DirectQuery to query an Oracle database and refresh reports without actually storing the data in the Power BI file (as would be done if "Import" was used instead of DirectQuery).
I have used SQL Tracing in the Database Instance to identify the SQL statement that Power BI executes
For the first visual in Power BI which shows total salary by Department, the Power BI module and SQL statement are identified as :
MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)
sqlid='c8a9qd2dzks8h'
SELECT * FROM (
SELECT
*
FROM
(
SELECT
"t1"."DEPARTMENT_NAME" "c6", SUM ( "t4"."SALARY" )
"a0"
FROM
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"$Table"."FIRST_NAME" as "FIRST_NAME",
"$Table"."LAST_NAME" as "LAST_NAME",
"$Table"."EMAIL" as "EMAIL",
"$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
"$Table"."HIRE_DATE" as "HIRE_DATE",
"$Table"."JOB_ID" as "JOB_ID",
"$Table"."SALARY" as "SALARY",
"$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"
LEFT OUTER JOIN
(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
"$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)
GROUP BY "t1"."DEPARTMENT_NAME"
)
"MainTable"
WHERE
(
NOT(
(
"a0" IS NULL
)
)
)
ORDER BY "a0"
DESC
,"c6"
ASC
) WHERE ROWNUM (lessthan) 1001
MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)
sqlid='dh7nbfqsy942q'
SELECT
"t1"."DEPARTMENT_NAME" "c6",
COUNT("t4"."EMPLOYEE_ID")
"a0"
FROM
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"$Table"."FIRST_NAME" as "FIRST_NAME",
"$Table"."LAST_NAME" as "LAST_NAME",
"$Table"."EMAIL" as "EMAIL",
"$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
"$Table"."HIRE_DATE" as "HIRE_DATE",
"$Table"."JOB_ID" as "JOB_ID",
"$Table"."SALARY" as "SALARY",
"$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"
LEFT OUTER JOIN
(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
"$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)
GROUP BY "t1"."DEPARTMENT_NAME"
Thus, every refresh runs a number of queries -- some to synchronise the schema from Oracle to Power BI and others to refresh the numbers to present in the Visuals.
This proves that the actual load of computing the GROUP BY and aggregations is in the *database instance* (because that is where the data actually resides) and not in the Power BI file (because no data is copied into the Power BI file)24 May, 2026
WAIT Clause for DMLs in 26.2
Oracle 26ai 26.2 now introduces the WAIT (and NOWAIT) Clause for INSERT / UPDATE / DELETE / MERGE DMLs. We have had a WAIT Clause for SELECT FOR UPDATE statements but not for these "simple" DML statements.
This is my Video Demo : Wait Clause for DMLs in 26.2
03 May, 2026
Domains and Annotations in 26ai
In a previous blog post I had demonstrated Domains for Columns and an Annotation for a table in 23ai.
Domains are "Data Use Case Domains" -- predefined Dictionary Objects that encapsulate properties and constraints for the data being stored in the target columns.
Here's the 26ai documentation on Data Use Case Domains : Data Use Case Domains
"An data use case domain is a high-level dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints."
"...a column can be declared both with a primitive data type such as NUMBER, as well as with a domain for data usage, such as "Temperature" or "Credit Score". Such a use case domain can optionally be associated with different usage properties such as check constraints, display properties, ordering rules, and others."
There are 109 Pre-Built Domains (as in 23.26.0 Free).
SQL> select owner, builtin, count(*) from all_domains group by owner, builtin order by 1,2;
OWNER BUILTIN COUNT(*) ------------ ----------- ---------- SYS TRUE 109 SQL>
But you can add your own Custom Domains. In this demonstration, I show two custom domains. Although the SALARY_D domain could have been a simple CHECK Constraint on the EMPLOYEES_TBL, I could use this domain in, say, DEPARTMENTS (for MAX_SALARY) or SALES_COMMISSION or CONTRACTOR_REMUNERATION etc.
I build the SALARY_D just as a simple example domain.
Annotations are "Metadata" -- that define and identify the data. Annotations are very good use cases for implementing AI where Natural Language queries from users are translated to SQL based on Annotations.
Here's the 26ai documentation on Annotations as a New Feature : Schema Annotations
"Oracle AI Database schema annotations are a lightweight declarative facility for developers to centrally register usage properties for database schema objects. Annotations are stored directly inside the database in dictionary tables alongside the data model definition and the data itself, and available to any applications in order to standardize behavior across common data, but are not interpreted by the database in any way. They should be thought of as lightweight standardized markup for database metadata, for use by applications to register and process extended and custom usage properties."
Here is one example of a table using Domains and Annotations :
SQL> @Domains_and_Annotations_Demo.sql
SQL> connect system/oracle
Connected.
SQL> grant create domain to hr
2 /
Grant succeeded.
SQL>
SQL> connect hr/oracle
Connected.
SQL>
SQL> drop table employees_tbl
2 /
Table dropped.
SQL>
SQL> create domain if not exists age_d as integer
2 constraint age_min check (age_d >= 18)
3 /
Domain created.
SQL>
SQL> create domain if not exists salary_d as number
2 constraint salary_max check (salary_d le 20000) -- I have changed the "less-than-or-equal-to" sign to "le" to preserve HTML parsing
3 /
Domain created.
SQL>
SQL>
SQL> create table employees_tbl
2 (
3 employee_id number generated by default on null as identity annotations (PrimaryKey, Sequence),
4 first_name varchar2(250) not null annotations (Display 'Employee First Name'),
5 middle_name varchar2(250) annotations (Display 'Employee Middle Name [optional]'),
6 last_name varchar2(250) not null annotations (Display 'Employee Last Name'),
7 join_age integer domain age_d annotations (Display 'Age at Start of Employment'),
8 employee_email_id varchar2(512) domain email_d annotations (Sensitive),
9 dept_id number not null annotations (Display 'Department ID'),
10 citizen_country varchar2(3) domain country_code_d annotations (Case 'UPPER', Display 'Citizenship Country ISO Code'),
11 resident_country varchar2(3) domain country_code_d annotations (Case 'UPPER', Display 'Residence Country ISO Code'),
12 current_salary number domain salary_d annotations (Display 'Employee Current Salary'),
13 salary_date date not null annotations (Display 'Current Salary Begin Date')
14 )
15 annotations (display 'Employees Table')
16 /
Table created.
SQL>
SQL>
SQL> describe employees_tbl
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER
FIRST_NAME NOT NULL VARCHAR2(250)
MIDDLE_NAME VARCHAR2(250)
LAST_NAME NOT NULL VARCHAR2(250)
JOIN_AGE NUMBER(38) HR.AGE_D
EMPLOYEE_EMAIL_ID VARCHAR2(512) SYS.EMAIL_D
DEPT_ID NOT NULL NUMBER
CITIZEN_COUNTRY VARCHAR2(3) SYS.COUNTRY_CODE_D
RESIDENT_COUNTRY VARCHAR2(3) SYS.COUNTRY_CODE_D
CURRENT_SALARY NUMBER HR.SALARY_D
SALARY_DATE NOT NULL DATE
SQL>
SQL>
SQL> set pagesize 600
SQL> set linesize 132
SQL> set long 2000
SQL> set longchunksize 1000
SQL> set serveroutput on
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEES_TBL')
2
SQL>
SQL> col columm_name format a21
SQL> col annotation_name format a21
SQL> col domain_name format a21
SQL> col annotation_value format a31
SQL>
SQL> select column_name, domain_name, annotation_name, annotation_value
2 from user_annotations_usage
3 where object_name = 'EMPLOYEES_TBL'
4 and object_type = 'TABLE'
5 order by column_name, annotation_name
6 /
COLUMN_NAME DOMAIN_NAME ANNOTATION_NAME ANNOTATION_VALUE
------------------------------ --------------------- --------------------- -------------------------------
CITIZEN_COUNTRY COUNTRY_CODE_D ADDRESS
CITIZEN_COUNTRY CASE UPPER
CITIZEN_COUNTRY DISPLAY Citizenship Country ISO Code
CURRENT_SALARY DISPLAY Employee Current Salary
DEPT_ID DISPLAY Department ID
EMPLOYEE_EMAIL_ID EMAIL_D PERSON_INFO
EMPLOYEE_EMAIL_ID SENSITIVE
EMPLOYEE_ID PRIMARYKEY
EMPLOYEE_ID SEQUENCE
FIRST_NAME DISPLAY Employee First Name
JOIN_AGE DISPLAY Age at Start of Employment
LAST_NAME DISPLAY Employee Last Name
MIDDLE_NAME DISPLAY Employee Middle Name [optional]
RESIDENT_COUNTRY COUNTRY_CODE_D ADDRESS
RESIDENT_COUNTRY CASE UPPER
RESIDENT_COUNTRY DISPLAY Residence Country ISO Code
SALARY_DATE DISPLAY Current Salary Begin Date
DISPLAY Employees Table
18 rows selected.
SQL>
SQL> -- the output above does NOT show the Custom Domains, only the preseeded COUNTRY_CODE_D and EMAIL_D
SQL> col domain_name format a21
SQL>
SQL> select name as domain_name, builtin, type
2 from user_domains
3 order by name
4 /
DOMAIN_NAME BUILTIN TYPE
--------------------- ----------- ----------
AGE_D FALSE REGULAR
SALARY_D FALSE REGULAR
SQL>
SQL>
SQL>
So, I have now created the Table using Domains and Annotations.
Now, I test the defined Domains for validation.
SQL> -- test the AGE_D domain for Join Age
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',17,'abc@co.com',10,'US','US',8000,sysdate)
/
SQL> 2 3 4 5 insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013676) involving column JOIN_AGE due to domain constraint HR.AGE_MIN of domain HR.AGE_D
violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',8000,sysdate)
/
2 3 4 5
1 row created.
SQL>
-- test the SALARY domain for Salary
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',32000,sysdate)
/
2 3 4 5 insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013674) involving column CURRENT_SALARY due to domain constraint HR.SALARY_MAX of domain
HR.SALARY_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',19000,sysdate)
/ 2 3 4 5
1 row created.
SQL>
-- test the EMAIL_D domain for Email Address
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@xyz',10,'US','US',32000,sysdate)
/
SQL> 2 3 4 5 insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013675) involving column EMPLOYEE_EMAIL_ID due to domain constraint SYS.SYS_DOMAIN_C0030 of
domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',19000,sysdate)
/
2 3 4 5
1 row created.
SQL>
SQL> -- test the COUNTRY_CODE_D
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'XYZ','US',19000,sysdate)
/
SQL> 2 3 4 5
1 row created.
SQL>
-- Note that the country_code_d does not seem tocheck validity against real ISO codes in my current 23.26.0 FREE release. It seems to check only that a string of characters is entered as a COUNTRY_CODE
So, the Domain Rules for AGE_D (custom domain defined by me), SALARY_D (custom domain defined by me) and EMAIL_D (system domain preseeded in the database) are all working as expected.15 February, 2026
Partition by Expression
Oracle 26.1 now allows using an Expression in the Partitioning Clause. In earlier releases, you could do this with a Virtual Column defined in the table. However, 26.1 does not require the Virtual Column if it is used simply for the Partitioning Clause.
Here is a demo in 19.12 comparing the two methods :
-- using a Virtual Column "ORDER_MONTH"
SQL> l
1 CREATE TABLE ORDERS_2026_old (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER,
5 order_month NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
6 )
7 PARTITION BY RANGE (order_month)
8 (
9 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
10 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
11 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
12 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
13* )
SQL> /
Table created.
SQL>
-- try the Expression in the Partitioning Clause
SQL> l
1 CREATE TABLE ORDERS_2026_new (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER
5 )
6 PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
7 (
8 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
9 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
10 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
11 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
12* )
SQL> /
PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
*
ERROR at line 6:
ORA-00904: : invalid identifier
SQL>
And now in 26.1 :
-- using the Virtual Column "ORDER_MONTH", works as expected
SQL> l
1 CREATE TABLE ORDERS_2026_old (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER,
5 order_month NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
6 )
7 PARTITION BY RANGE (order_month)
8 (
9 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
10 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
11 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
12 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
13* )
SQL> /
Table created.
SQL>
-- using the Expression for the Partitioning Clause
SQL> l
1 CREATE TABLE ORDERS_2026_NEW (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER
5 )
6 PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
7 (
8 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
9 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
10 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
11 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
12* )
SQL> /
Table created.
SQL>
SQL> insert into orders_2026_new values (1, sysdate, 1);
1 row created.
SQL>
QL> insert into orders_2026_new values (2, to_date('15-JUN-2026','DD-MON-RRRR'),2);
1 row created.
SQL> commit;
Commit complete.
SQL>
-- gather statistics
SQL> exec dbms_stats.gather_table_stats('','ORDERS_2026_NEW');
PL/SQL procedure successfully completed.
SQL>
-- check number of rows in each partition
SQL> col partition_name format a32
SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'ORDERS_2026_NEW'
4 /
PARTITION_NAME NUM_ROWS
-------------------------------- ----------
ORDERS_Q1 1
ORDERS_Q2 1
ORDERS_Q3 0
ORDERS_Q4 0
SQL>
-- check rows in the specific partitions
-- get all the rows where the month number is less than 4
SQL> select * from orders_2026_new
2 partition for (3)
3 /
ORDER_ID ORDER_DATE CUSTOMER_ID
---------- ------------------ -----------
1 15-FEB-26 1
SQL>
-- get all the rows where the month number is less than 7
SQL> select * from orders_2026_new
2 partition for (6)
3 /
ORDER_ID ORDER_DATE CUSTOMER_ID
---------- ------------------ -----------
2 15-JUN-26 2
SQL>
Since partitioning is by Month Number (would have been the Virtual Column ORDER_MONTH) in the 19c example, I can use the "PARTITION FOR (<month_number>)" in my SELECT statement to query a specific partition.
I can confirm that ORDER_ID 1 is in the first partition (Month Number less than 4) and ORDER_ID 2 is in the second partition (Month Number less than 7)
08 February, 2026
The new FILTER clause in Aggregations
Oracle 26.1 introduces the new FILTER clause that can be used in aggregations.
Thus, for example, in a SUM aggregation you can use FILTER WHERE ...
Here is a quick example :
SQL> create table obj_list as select * from dba_objects;
Table created.
SQL> select sum(object_id) from obj_list;
SUM(OBJECT_ID)
--------------
2604221816
SQL> select object_type, sum(object_id) from obj_list group by object_type order by 1;
OBJECT_TYPE SUM(OBJECT_ID)
----------------------- --------------
CLUSTER 3036
CONSUMER GROUP 435231
CONTEXT 1048288
...... deleted a number of object types
SEQUENCE 7551346
SYNONYM 473741064
TABLE 44661426 --> sum of object_ids for object_type TABLE
TABLE PARTITION 15047252
TABLE SUBPARTITION 603760
...... deleted a few more object types
XML SCHEMA 511732
46 rows selected.
SQL> select sum(object_id) filter (where object_type = 'TABLE') as Table_ObjID_Sum from obj_list;
TABLE_OBJID_SUM
---------------
44661426
SQL>
1. Oracle Documentation New Features Guide: Aggregation Filters
2. Oracle Documentation SQL For Application Developers : 14.2 Aggregation Filters
3. Markus Winand's Modern SQL Reference : Filter
Subscribe to:
Posts (Atom)