how to change column default value to no default in 11gr2
Have the question as follows:
create table scott.t1(col1 varchar2(1) default null, col2 varchar2(10));
create table scott.t2(col1 varchar2(1), col2 varchar2(10));
create table scott.tt (table_name varchar2(35), column_name varchar2(35), data_default varchar2(20));
begin
for a in (select table_name, column_name, DATA_DEFAULT from DBA_TAB_COLS where owner = 'SCOTT' and table_name in ('T1', 'T2') ) loop
insert into scott.tt values(a.table_name, a.column_name, a.data_default);
end loop;
end;
/
select * from scott.tt order by 1,2,3;
TABLE_NAME COLUMN_NAME DATA_DEFAULT
T1 COL1 null
T1 COL2
T2 COL1
T2 COL2
The question is
without dropping T1, how to remove the default value "null" here to make T1 the same as T2???
Can any guru shed some light on it? Thanks a lot.
create table scott.t1(col1 varchar2(1) default null, col2 varchar2(10));
create table scott.t2(col1 varchar2(1), col2 varchar2(10));
create table scott.tt (table_name varchar2(35), column_name varchar2(35), data_default varchar2(20));
begin
for a in (select table_name, column_name, DATA_DEFAULT from DBA_TAB_COLS where owner = 'SCOTT' and table_name in ('T1', 'T2') ) loop
insert into scott.tt values(a.table_name, a.column_name, a.data_default);
end loop;
end;
/
select * from scott.tt order by 1,2,3;
TABLE_NAME COLUMN_NAME DATA_DEFAULT
T1 COL1 null
T1 COL2
T2 COL1
T2 COL2
The question is
without dropping T1, how to remove the default value "null" here to make T1 the same as T2???
Can any guru shed some light on it? Thanks a lot.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Inserting, updating or otherwise modifying the content of the "TT" table doesn't change the default value of a column.
It could change a report that looks at column definitions, but that's not what the question asked.
It could change a report that looks at column definitions, but that's not what the question asked.
ASKER
Thanks for the tips. I also checked some doc and links. However I still would like to try the alternatives. Maybe the question is equavilent to that
1) is there any safe way to update the table dba(user,all)_tab_cols?
2) As sdstuber mentioned, drop/re-create the column and re-arrange the order to the original. Could you please show me an example for it or a link?
Thanks
1) is there any safe way to update the table dba(user,all)_tab_cols?
2) As sdstuber mentioned, drop/re-create the column and re-arrange the order to the original. Could you please show me an example for it or a link?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the answers
(a.table_name, a.column_name, decode(upper(a.data_defaul