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.
Top comments (2)
Wonderful, very useful as usual!
استاد خالقی، چاکریم