A repost here from my grumpy old dba blog:

inconsistent/unpredictable results make me grumpy ...

Here is a strange one. Why does one query get an ORA-00904 but the
same query when used as part of an IN apparently get ignored ( or not
noticed as an error )?

This query returns an error:

select table_name from dba_users;
Error at line 1
ORA-00904: "TABLE_NAME": invalid identifier


This query executes ( not very well but ... ):
select * from dba_tables where table_name in
( select table_name from dba_users );

This reproduces in 11.1.0.7.6 and 10.2.0.4.

Is this a well known bug already or ( for some reason ) expected
behavior?

I am just starting to research it now ... unfortunately a developer
has some bad code running in production with this type of code.

On 30/06/11 20:17, John Hurley wrote:
[...]

table_name column?

So if you use synonyms

i.e. (untested)

Select * from dba_tables dt where dt.table_name in
(select du.table_name from dba_users du );

You should get the same error.

[...]

--
S. Anthony Sequeira
+44 7542 455 233
++
If you have to ask what jazz is, you'll never know.
-- Louis Armstrong
++

"John Hurley" <hurleyjohnb (AT) yahoo (DOT) com> wrote


It's expected behaviour. Think correlated subqueries.

If a column referenced inside a subquery does not belong to any of the
tables in the subquery then Oracle assumes that it is a correlating column
that comes from the next layer out.


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

On 30/06/11 21:23, S. Anthony Sequeira wrote:
[...]
B****er I meant aliases.

[...]
--
S. Anthony Sequeira
+44 7542 455 233
++
The first sign of maturity is the discovery that the volume knob also
turns to
the left.
++

Jonathan Lewis wrote,on my timestamp of 1/07/2011 6:23 AM:

Yup, that's what I'm seeing as well.
(A case of Oracle trying to be too smart for its own good?)

"Noons" <wizofoz2k (AT) yahoo (DOT) com.au> wrote


No, it's standard SQL

If it didn't do this, how would you write correlated subqueries ?

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Jonathan Lewis wrote,on my timestamp of 1/07/2011 8:43 PM:

You're right, of course. I always write those using aliases for table names and
add them to the column names, makes for much easier reading. But it's not
strictly necessitated by the standard.

On Jun 30, 3:17*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
This is expected behavior because of the SQL scope rules; You failed
to alias your sub-query as specified in the SQL manual. The outer
table columns are visible within the sub-query,

HTH -- Mark D Powell --

This is expected result from my understanding. The "tablename" in the
subquery , is taking the scope of the parent since it is unresolved.
You can reproduce similar behavior using any two tables.

Eg:

select * from emp where empno in (select empno from dept); --runs fine

select empno from dept; --fails with ora-00904

This is no different (from variable scoping point of view) from using
the parent column in the "where" clause of the subquery (which is what
we typically see).

- Madhu
On Jun 30, 3:17*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:

On Jun 30, 12:17*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
I get the 904 in both in my 10.2.0.4 environment, as sys. Perhaps you
wind up with some strange predicate in the way you are defining your
dba privileges? Maybe setting event 10730 (undocumented way for vpd
predicates) will give a tracing clue.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...anking-bypass/