Francisco,

GOD BLESS YOU. I got it to work. The output's format is not the way i
want, but i edit it in Excel and it works out. God bless you 100 times. Thank
you very much. Thanks for Mike too who tried to help.

Best regrads,

Thanks,

Al

Quoting Francisco Morales <[Email address protected]

> **A LazyDBA.com subscriber has responded to your lazydba.com post**
> **LazyDBA.com mail shield has forwarded you this email,
> **and removed any attachments, and kept your email address secret
> **from this person, and any viruses/trojans.
> **If you reply to this email, the person will see your email address as
> normal
> **Anything below this line is the original email text
>
>
> It seems that the where clause with the type long works fine. However,
> it seems that it cannot be accepted in the concatenation. I took the
> data_default out of the concatenation an I get results like the
> following:
> 1 select
> 2 'alter table ' || owner || '.'|| table_name || ' modify '|| '( '
> ||
> column_name
> 3 || ' default ' , data_default , ' ) ' || ' ;'
> 4 from dba_tab_columns
> 5 where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'OUTLN', 'WMSYS',
> 'DBSNMP')
> 6 and owner not like '%SYS%'
> 7* and data_default is not null
>
> alter table GENERAL.GORFILX modify ( GORFILX_DOWNLOAD_START_IND
> default
> 'N'
> ) ;
>
> alter table ODM.ODM_MINING_FUNCTION_SETTINGS modify (
> CREATION_TIMESTAMP
> default
>
> SYSDATE
>
> ) ;
> --------------
>
> >>> "ab265" [Email address protected]
> 02/10/08 11:07 AM >>>
> 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
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
>


Oracle LazyDBA home page