Oracle AI Database 26ai(23.26.4): Mixing SELECT * with Other Columns and Expressions

 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.