Search code examples
sqloracle-databasedata-dictionary

Select column_name from cols return nothing although column exists

i am trying to execute this statement in Oracle 9i. However it seems that the result set is empty although J am very sure that there are many columns with the name ID.

select * from cols where column_name like '%ID%';

Also, the following statement returns an empty result set.

select * from cols;

May I ask if this could be due to user privilege?

Thanks!

Solution

  • The Oracle Reference says of COLS:

    "COLS is a synonym for USER_TAB_COLUMNS."

    This suggests a simple answer to your conundrum: you are connected to the database through a user which owns no tables (or views). Either change your user or try selecting from ALL_TAB_COLUMNS (which shows results from all the tables/views you have privileges on).