Search code examples
oracleviewintrospection

Oracle: How can I introspect a view?

How can I introspect a view in Oracle? For example, if I have

create view v as select foo, bar from baz

I would like to know that foo and bar are the first and second columns selected.

Solution

  • You can use all_tab_columns also for views. So the first two columns of view v will be something like:

    SELECT *
  FROM all_tab_columns
 WHERE TABLE_NAME = 'V' AND column_id IN (1, 2);

    Then you can do ask for specific columns:

     SELECT COUNT(*) TOTAL
  FROM all_tab_columns
 WHERE TABLE_NAME = 'V' AND (table_name,column_id) IN (('FOO',1), ('BAR',2));

    If total is 2 means that foo and bar are the first and second columns selected. You can make this more readable with a case or decode:

    SELECT CASE WHEN TOTAL = 2 THEN 1 ELSE 0 END RESULT FROM
(SELECT COUNT(*) TOTAL
      FROM all_tab_columns
     WHERE TABLE_NAME = 'V' AND (table_name,column_id) IN (('FOO',1), ('BAR',2)));