MySQL developer here -- Nesting with select * finicky in Oracle 10g?

I'm writing a simple diagnostic query then attempting to execute it in the Oracle 10g SQL Scratchpad. EDIT: It will not be used in code. I'm nesting a simple "Select *" and it's giving me errors.

In the SQL Scratchpad for Oracle 10g Enterprise Manager Console, this statement runs fine.

SELECT *  FROM v$session sess, v$sql     sql  WHERE sql.sql_id(+) = sess.sql_id and 开发者_运维技巧sql.sql_text <> ' ' 

If I try to wrap that up in Select * from () tb2 I get an error, "ORA-00918: Column Ambiguously Defined". I didn't think that could ever happen with this kind of statement so I am a bit confused.

 select * from
 (SELECT *  FROM v$session sess, v$sql     sql  WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' ')
 tb2

You should always be able to select * from the result set of another select * statement using this structure as far as I'm aware... right?

Is Oracle/10g/the scratchpad trying to force me to accept a certain syntactic structure to prevent excessive nesting? Is this a bug in scratchpad or something about how oracle works?

When Oracle parses a SELECT *, it expands it out to an actual list of the columns to be selected. Since your inline view contains two columns named SQL_ID, this results in an ambiguous reference.

Interestingly, using ANSI join syntax seems to cause it to alias the duplicate column names automatically, and therefore avoids the error.

select * from
(select * from v$session sess left outer join v$sql sql on sql.sql_id=sess.sql_id and sql.sql_text <> ' ')

Incidentally, it's not clear to me why you chose that condition on sql_text. I don't expect that column would ever contain a single space. Are you really trying to filter out NULLs? If so, why use an outer join at all?

One of the general rules of thumbs at my place of employment is that SELECT * is never allowed. Explicitly define what columns you need; not only is it more readable, but less likely to have issues down the road