2

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!

APC
  • 144,005
  • 19
  • 170
  • 281
Oh Chin Boon
  • 23,028
  • 51
  • 143
  • 215

1 Answers1

7

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).

APC
  • 144,005
  • 19
  • 170
  • 281
  • I think somewhere along the line before your post a member mentioned that this could also be affected by a feature called the Virtual Private Database that is since version Oracle 8i, however i think the post has been removed, APC would you have any comments on this? Kindly. :) – Oh Chin Boon May 26 '11 at 06:50
  • @ChinBoon - Virtual Private Database (AKA Fine-Grained Access Control) is a mechanism for invisibly restricting access to rows of data - and columns after 10gR2 - by defining additional filters which are applied to all DML statements. Check out DBMS_RLS to find out more. I don't think it is relevant to your situation, because VPD would not be applied to the data dictionary. – APC May 26 '11 at 10:24