The answer explains why ROWID can't be in the SELECT list of an outer query against an inline view with CONNECT BY. But it doesn't answer the question in the OP's post - which is different from the question in his title. Namely, why doesn't SELECT * work against such views.
The OP's query, selecting * from the inline view, "should work" (the quotes around "should work" will become clear later). * should be expanded to the list of columns resulting from the view - it should not resolve any of those names to a pseudocolumn of the outer query. (And, to state the obvious, a trivial workaround is to give an alias like ROWID_ to the ROWID pseudocolumn in the inline view.)
Similar queries using other pseudocolumns, like LEVEL and ROWNUM, are transformed correctly - * is expanded to the column names returned by the inline view, as column names IN DOUBLE-QUOTES (which means they can't be resolved to pseudocolumns, which would take precedence; they must be resolved as column names or aliases from the view).
To understand what I'm saying, consider the following three queries:
select LEVEL from (select deptno as "LEVEL", count(*) as ct from scott.dept group by deptno);
select "LEVEL", ct from (select deptno as "LEVEL", count(*) as ct from scott.dept group by deptno);
select * from (select deptno as "LEVEL", count(*) as ct from scott.dept group by deptno);
The first one fails with "CONNECT BY clause required in this query block", but the second query succeeds, and the third succeeds too - and the 10053 trace shows that the third query is transformed into the second query, not the first. (Essentially; in fact the transformed query will also have an alias for the inline view, etc.)
Change LEVEL to ROWID in all the queries above, in all places - now ALL THREE queries fail. The specific question boils down to this:
select "ROWID" from (select deptno as "ROWID", count(*) as ct from scott.dept group by deptno);
fails with "ORA-01446: cannot select ROWID from ..." Which is odd - we explicitly selected "ROWID" (in double-quotes), not ROWID. Why is this handled differently from the "LEVEL" vs LEVEL example?The answer is found in the documentation
- Note 2 to the "Oracle SQL Reserved Words" section. In general, reserved words can
be used as column names, if put in double-quotes; but ROWID is a notable exception:Note 2: You cannot use the uppercase word ROWID, either quoted or nonquoted, as a column name. However, you can use the uppercase word as a quoted identifier that is not a column name, and you can use the word with one or more lowercase letters (for example, "Rowid" or "rowid") as any quoted identifier, including a column name. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Oracle-SQL-Reserved-Words.html#GUID-55C49D1E-BE08-4C50-A9DD-8593EB925612
The documentation doesn't explain why
Oracle needed this exception to the rule about reserved words in double quotes as column names, but at least it is clearly documented.