1

I need to be able to reconstruct a table column by using the column data in DBA_TAB_COLUMNS, and so to develop this I need to understand what each column refers to. I'm looking to understand what DATA_TYPE_MOD is -- the documentation (http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm#I1020277) says it is a data type modifier, but I can't seem to find any columns with this field populated or any way to populate this field with a dummy column. Anyone familiar with this field?

drouleau
  • 35
  • 5

2 Answers2

1

Data_type_mod column of the [all][dba][user]_tab_columns data dictionary view gets populated when a column of a table is declared as a reference to an object type using REF datatype(contains object identifier(OID) of an object it points to).

  create type obj as object(
    item number
  ) ;

  create table tb_1(
    col ref obj
  )


  select t.table_name
       , t.column_name
       , t.data_type_mod
   from user_tab_columns t
  where t.table_name = 'TB_1'

Result:

table_name   column_name   data_type_mod
-----------------------------------------
TB_1         COL           REF
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
0

Oracle has a PL/SQL package that can be used to generate the DDL for creating a table. You would probably be better off using this.

See GET_DDL on http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414

And see also: How to get Oracle create table statement in SQL*Plus

Community
  • 1
  • 1
WW.
  • 23,793
  • 13
  • 94
  • 121
  • Yes I'm aware of this package. The reason I need to understand these columns is so that when I am adding a column or modifying an existing column I do not need to drop and create the table, I can just run an ALTER. I can generate the alter by comparing columns of DBA_TAB_COLUMNS and data in my own custom table storing all table columns. – drouleau Sep 23 '13 at 23:14