inconsistent results make me grumpy ( where is the 904? )

#11

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

Quote:

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.

It is not a bug; Oracle is taking table_name from dba_tables and
trying to select that from dba_users since you didn't qualify the
column name. If you try this:

SQL> select * from dba_tables where table_name in
2 ( select u.table_name from dba_users u);

( select u.table_name from dba_users u)
*
ERROR at line 2:
ORA-00904: "U"."TABLE_NAME": invalid identifier

SQL>

it fails, as expected as table_name is now associated with dba_users,
not dba_tables as it was before. Since table_name was uniquely
identified in the query construct (it appears only in dba_tables)
Oracle didn't complain; it's as though you wrote

select * from dba_tables where table_name in
( select 'DBA_USERS' from dba_users);

except that Oracle substituted the current table_name into the
subquery for each record it returned, treating it as though it were a
constant.

David Fitzjarrell

#12

On Jul 1, 9:07*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:

Quote:

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

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.

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/

Shoot, I must've typo'd the second one. It "works" now.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...ide/?comic-con

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

inconsistent results make me grumpy ( where is the 904? )

comp.databases.oracle.server comp.databases.oracle.server

Re: inconsistent results make me grumpy ( where is the 904? ) - 07-01-2011 , 02:55 PM

Re: inconsistent results make me grumpy ( where is the 904? ) - 07-01-2011 , 05:44 PM