CODEWITHSUNDEEP
databaseoracle-databaseuser-defined-typesdata-dictionary
ihadanny
ihadanny

Reputation: 4483

detect oracle UDT columns

I have a query that fetches the schema of the a table from the oracle data dictionary, and I want to be able to detect which of its columns is a UDT - user-defined type column. is the following SQL reasonable?

select * from
all_tab_cols
    join all_types on all_tab_cols.DATA_TYPE_OWNER = all_types.owner
    and all_tab_cols.DATA_TYPE = all_types.TYPE_NAME
where ALL_TAB_COLS.owner not in ('SYS', 'SYSTEM', 'XDB')

Upvotes: 0

Views: 216

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Yes that is pretty much what you need. Variations really depend on what you consider to be worth including or not. For example.

  • Some complex datatypes are owned by PUBLIC (in ALL_TAB_COLS) but their true type will be owned typically by SYS in ALL_TYPES
  • There are other schemas you might want to include/exclude (MDSYS, APEX..., etc etc)

Upvotes: 1

Related Questions