Re: DBA_ tables

>In article <12SEP94.22114086_at_nauvax.ucc.nau.edu> kkc_at_nauvax.ucc.nau.edu writes:

.. stuff deleted... However, I am experiencing a very frustrating phenomenon:
>When I say "select table_name from all_tables" I can get a list of all the
>table names in the database. As soon as I try to clarify the select
>statement by saying "select table_name from all_tables where table_name =
>CUSTOMER" I get the 'invalid column name' error even though table_name
>is in fact a valid column name. It's like as soon as I specify a 'where'
>I am missing a particular clarifier or something? Can anyone shed some
.. more stuff deleted

The datatype for table_name in all_tables is varchar(2), so when you are trying to do an equality (or other) match, the string you are matching on must be in single quotes. Below is an example of what I mean. The error message _is_ a tad misleading. Yes, it would be nice if ORACLE came with an error message interpreter.

SQL> select table_name from all_tables where table_name = 'DUAL';

                                                                  
TABLE_NAME                                                        
------------------------------                                    
DUAL                                                              
                                                                  
SQL> select table_name from all_tables where table_name = "DUAL"; 
select table_name from all_tables where table_name = "DUAL"       
                                                     *            
ERROR at line 1:                                                  
ORA-00904: invalid column name                                    

Hope this helps. Received on Wed Sep 14 1994 - 18:45:12 CEST