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

Question

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?

Answer 1

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     

Answer 2

Instead of table names you can use aliases

SELECT FirstName, LastName, City, c.*
FROM Customers c;

Even on SQL server 2008 you get into trouble, when your query evolves

SELECT FirstName, LastName, City, *
FROM Customers 
ORDER BY Firstname ;

you will have to change it to

SELECT FirstName, LastName, City, c.*
FROM Customers c 
ORDER BY c.Firstname ;