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.
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';
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.
It appears, at least in 11g, that you cannot access the data dictionary tables from PL/SQL. Running any select on all_tab_columns inside PL/SQL always returns no results. Attempting to access dba_tab_columns will not compile, because the compiler believes the table (or view) does not exist.
I'd love to see how to access the data dictionary from PL/SQL.
