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.

Shouldn't the value you are searching for in column_name be uppercase? — Joe W, Jun 28 '13 at 12:39

Robert · Accepted Answer · 2013-06-28T12:46:14.920

11

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';

edited Jun 28 '13 at 12:46

answered Jun 28 '13 at 12:38

Robert

25,425
8
67
81

Oracle always store column names as well as table names in upper case; so upper is necessary on 'foo' – Dmitry Bychenko Jun 28 '13 at 12:45
Thanks! That was it! I will vote it in 4 minutes when I am allowed to. – lostinthebits Jun 28 '13 at 12:46
@DmitryBychenko I've change the answer. Thanks! – Robert Jun 28 '13 at 12:46
1

The upper on column_name and table_name are unneeded and really the other two uppers are unneeded as one is already all uppercase and its easy to just change the the other one to uppercase and not have to do any work on the system to change the case. – Joe W Jun 28 '13 at 12:48
Wait is there a difference between accepting the answer and voting for it? – lostinthebits Jun 28 '13 at 12:49
@lostinthebits Yes! You can accept only one and only answers to your own questions. – Michel de Ruiter Jun 15 '16 at 08:14

score 1 · Answer 2 · answered Jun 28 '13 at 12:46

1

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.

answered Jun 28 '13 at 12:46

Mikhail

1,540
2
13
13

score 0 · Answer 3 · answered Jun 30 '17 at 17:16

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.

Oracle SQL: selecting from all_tab_columns does not find existing column

3 Answers3

Linked