Oracle sql: selectng of all_tab_columns does not find exisintg 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 the result. I expect 1.

But if I run the following query, I get a lot of (expected) lines:

 select foo from VIEW0; 

Why? I suppose I'm making some kind of stupid syntax error, or my understanding goes away.

Answer 1

This is probably because you have a case-sensitive setting.

Try adding the UPPER function as shown 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 tables, views, and clusters available to the current user. Check if the user you are executing this query has access to the desired table.

Answer 3

It seems at least in 11g that you cannot access data dictionary tables from PL / SQL. Running any selection in all_tab_columns inside PL / SQL always does not return results. An attempt to access dba_tab_columns will not compile because the compiler considers that the table (or view) does not exist.

I would like to see how to access the data dictionary from PL / SQL.