How does one cheaply validate the existance of a column in a table in another schema with Oracle?

Question

The environment is Oracle 9 & 10. I do not have DBA level access.

The problem is to verify that a specific column exists in a specific table, in another schema.

There are two cases to deal with.

1. Another schema in the same instance
2. A schema in a different instance, using a db_link

Given my schema FRED and another schema BARNEY, I tried something like this

SELECT 1
FROM BARNEY.USER_TAB_COLS
WHERE TABLE_NAME = 'SOME_TABLE' 
  AND COLUMN_NAME = 'SOME_SPECIFIC_COLUMN'

Which yielded [1]: (Error): ORA-00942: table or view does not exist

After vegging on this awhile, I realized that USER_TAB_COLS, is not really a table. It is a view. I have been selecting from tables all along, but not from a view.

I tried the same thing with my db_link, and was surprised to see data come back. A db_link has an embedded schema_name/password in it, so it seems reasonable to me that it worked, as it effectively logs in to the other schema, which should make the views reachable.

Having Googled around, and worn out my eyeballs on on the mountain of Oracle doc, I am looking for someone to point me in the correct direction, or at least point out what I am missing.

What techniques are available for getting user table related metadata from a schema in the same instance in order to validate that a specific column exists?

Thanks in advance.

Evil.

+1 for good answers. Thank you.

Answer 1

You would use all_tab_columns for that.

But beware that you'll only see what you are allowed to see.

Answer 2

You can use the following query:

SELECT 1
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'SOME_TABLE' 
  AND COLUMN_NAME = 'SOME_SPECIFIC_COLUMN'
  AND OWNER = 'BARNEY';

(User_Tables and User_Tab_Cols are just views on all_tables and all_tab_coumns with a where owner = <Current User> attached to it)

If you're allowed to see the Barney's some_table (i.e. you have been GRANTed at least SELECT privileges on it), then you'll know if the column is there. If you have no rights on the table, you won't be able to get meta information on it.

Answer 3

Same instance, different schema:

Select Count(*)
From   all_tab_cols
Where  owner       = 'BARNEY'               and
       table_name  = 'SOME_TABLE'           and
       column_name = 'SOME_SPECIFIC_COLUMN';

The count(*) has the advantage of always returning a single row with a value of either 1 or 0, so you do not have to deal with NO_DATA_FOUND errors in PL/SQL.

Across a DB Link, same schema as the one you connect as:

Select Count(*)
From   user_tab_cols@MY_DB_LINK
Where  table_name  = 'SOME_TABLE'           and
       column_name = 'SOME_SPECIFIC_COLUMN';

Across a DB Link, different schema than the one you connect as:

Select Count(*)
From   all_tab_cols@MY_DB_LINK
Where  owner       = 'BARNEY'               and
       table_name  = 'SOME_TABLE'           and
       column_name = 'SOME_SPECIFIC_COLUMN';

Answer 4

As with the other replies, normally I use ALL_TAB_COLUMNS for a query like this. But that will only show columns in tables where you have SELECT. And it's select on that column -- in the unlikely event that they've implemented column-level privileges for that table, you may be able to see the table, but not see the specific column of interest. For most of us, that's extremely rare.

DBA_TAB_COLUMNS will show all columns, but you'll need select on it granted to your schema by your DBA. (Actually, you'll need a grant on ALL_TAB_COLUMNS to use it, but that's common in most shops). The DBMS_METADATA PL/SQL Built-in package can also be used, with similar limitations, but I think you'll find it more complicated.

Of course, you can also just try to select a record from barney.some_table.some_column@my_dblink (or whatever pieces of that you're interested in). And then handle the exception. Ugly, I wouldn't recommend it in most situations.