Experts,

Please, help me in doing that. I am trying to generate a sql script that
will give me all the default values for the columns in my datatypes. My problem
is that DATA_DEFAULT column in DBA_TAB_COLUMNS table has LONG datatype. Please,
help me to get those values. Look below and you will understand what i am doing.

select
'alter table ' || owner || '.'|| table_name || ' modify '|| '( ' || column_name
|| ' default ' || data_default || ' ) ' || ' ;'
from dba_tab_columns
where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'OUTLN', 'WMSYS', 'DBSNMP')
and owner not like '%SYS%'
and data_default is not null;

ORA-00932
ORA-00932: inconsistent datatypes: expected number got long

SQL> DESC DBA_TAB_COLUMNS

OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)

Thanks,

Al

Oracle LazyDBA home page