Oracle AI Database 26ai(26.23.4) introduces a useful SQL enhancement that allows the * wildcard to be combined with explicit column names, constants, and SQL expressions in the same SELECT list.

Before the 23.26.4 release, Oracle did not allow * to be used alongside other select-list items in this way:

Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free Version 23.26.3.0.0 SQL> select id,*,fname from person; select id,*,fname from person * ERROR at line 1: ORA-00936: missing expression Help: https://docs.oracle.com/error-help/db/ora-00936/ The asterisk cannot be combined with explicit column names in this statement, so the query fails with ORA-00936.

Now, the same query executes successfully on Oracle AI Database 23.26.4:

Oracle AI Database 26ai Enterprise Edition Release 23.26.4.0.0 - Production Version 23.26.4.0.0 SQL> select id,*,fname from person; ID ID FNAME LNAME SAL FNAME --- --- ---------- --------------- ---------- ---------- 1 1 Vahid Yousefzadeh 5 Vahid 2 2 Nader DastNeshan 6 Nader 3 3 Kourosh Barmak 6 Kourosh

Oracle expands * into all columns from the PERSON table at its position in the select list. As a result, ID and FNAME appear twice: once through the explicit column references and once through the wildcard expansion.

Additional Examples:

1.Using Multiple Wildcards 

SQL> select *,* from person; ID FNAME LNAME SAL ID FNAME LNAME SAL --- ---------- --------------- --- --- ---------- --------------- --- 1 Vahid Yousefzadeh 5 1 Vahid Yousefzadeh 5 2 Nader DastNeshan 6 2 Nader DastNeshan 6 3 Kourosh Barmak 6 3 Kourosh Barmak 6

Each occurrence of * expands independently, returning the complete set of table columns twice.

2. Combining * with SQL Functions 

SQL> select *,upper(fname) ,lower(fname) from person; ID FNAME LNAME SAL UPPER(FNAM LOWER(FNAM ---------- ---------- --------------- ---------- ---------- ---------- 1 Vahid Yousefzadeh 5 VAHID vahid 2 Nader DastNeshan 6 NADER nader 3 Kourosh Barmak 6 KOUROSH kourosh

This makes it possible to retrieve the original table data and additional calculated values in a single query without explicitly listing every column.

3. Combining Constants with * 

SQL> select 'test','test' as test ,* from person; 'TES TEST ID FNAME LNAME SAL ---- ---- ---------- ---------- --------------- ---------- test test 1 Vahid Yousefzadeh 5 test test 2 Nader DastNeshan 6 test test 3 Kourosh Barmak 6

In this example, Oracle returns two constant expressions followed by all columns from PERSON. The constant values are repeated for every row in the result set.