Oracle SQL: selecting from all_tab_columns does not find existing column

Question

If I run the following query:

select count(*) from all_tab_columns
        where column_name = 'foo'
        and table_name = 'VIEW0';

I get 0 for a result. I expect 1.

But if I run the following query I get many (expected) rows returned:

select foo from VIEW0;

Why? I'm assuming I'm making some dumb syntax mistake or my understanding is way off.

Answer 1

Probably the reason is that you have case sensitive setting.

Try to add UPPER function as below.

select count(*) from all_tab_columns
        where column_name = upper('foo')
        and table_name = 'VIEW0';

Answer 2

ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user. Check, if user under whom you running this query have access to the desired table.