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

Posted by John Sullivan on Stack Overflow See other posts from Stack Overflow or by John Sullivan
Published on 2010-05-14T15:24:38Z Indexed on 2010/05/14 15:34 UTC
Read the original article Hit count: 203

Filed under:
|

I am writing a simple diagnostic query I will execute in my Oracle 10g scratchpad. I am trying to do this as part of a step to build the query.

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?

© Stack Overflow or respective owner

Related posts about oracle10g

Related posts about Oracle