SELECT statement with an explicit list of columns and then an asterisk (*)

oracle

SQL Server allows to use SELECT statements similar to:

SELECT FirstName, LastName, City, *
FROM dbo.Customers

When I try to execute such a query on Oracle database, I get the following error:

ORA-00936: missing expression
00936. 00000 – "missing expression"

Doesn't Oracle support such queries?

Best Answer

Only if you prefix the asterisk with the table name:

select dummy, dual.* from dual;

DUMMY DUMMY 
----- ----- 
X     X

Which limits it's use to tables not joined with the using syntax:

select dummy, d1.* from dual d1 join dual d2 using(dummy)

SQL Error: ORA-25154: column part of USING clause cannot have qualifier
25154. 00000 -  "column part of USING clause cannot have qualifier"
*Cause:    Columns that are used for a named-join (either a NATURAL join
           or a join with a USING clause) cannot have an explicit qualifier.
*Action:   Remove the qualifier.

select d2.dummy, d1.* from dual d1 join dual d2 on(d1.dummy=d2.dummy);

DUMMY DUMMY 
----- ----- 
X     X
Related Question