Setting the default to NULL changes to behavior to be the same as if a
default value had not been set. However, in dba_tab_columns, the
default_data column would indicate null. If the default value was never set,
the column would be empty (null in type, not name).
The example below demonstrates the behavior:
create table test
(col1 char(4),
col2 char(4) default 'AAAA');
insert into test values (NULL, NULL);
insert into test values ('1', NULL);
insert into test values ('2', 'BBBB');
select * from test;
COL1 COL2
---- ----
1
2 BBBB
insert into test (col1) values ('3');
select col1, length(col1), col2, length(col2)
from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
---- ------------ ---- ------------
1 4
2 4 BBBB 4
3 4 AAAA 4
alter table test
modify col2 default NULL;
insert into test (col1) values ('4');
select col1, length(col1), col2, length(col2)
from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
---- ------------ ---- ------------
1 4
2 4 BBBB 4
3 4 AAAA 4
4 4
alter table test modify col2 default null
select table_name, column_name, data_default
from user_tab_columns
where table_name = 'TEST';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
---------- ----------- ------------
TEST COL1
TEST COL2 null
SQL>
SQL> alter table test
2 modify col2 default 'NULL';
SQL> insert into test (col1) values ('5');
SQL> select col1, length(col1), col2, length(col2)
2 from test;
COL1 LENGTH(COL1) COL2 LENGTH(COL2)
---- ------------ ---- ------------
1 4
2 4 BBBB 4
3 4 AAAA 4
4 4
5 4 NULL 4
select table_name, column_name, data_default
from user_tab_columns
where table_name = 'TEST';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
---------- ----------- ------------
TEST COL1
TEST COL2 'NULL'
-----Original Message-----
Sent: Friday, October 04, 2002 2:38 PM
To: Multiple recipients of list ORACLE-L
Louis,
I still have not convinced myself that we have the proper answer.
If you query the DBA_TAB_COLUMNS table DATA_DEFAULT column you will see
that the original NON default created column has a null or blank as the
value for the data_default column. If the default is set to NULL then
the word NULL appears as the data_default value for the column. What if
the column was a char(4) column then it would default to the value"NULL"
which is not the same as blank or nothing.
Still digging for an answer.
Ron
ROR mô¿ôm
>>> [EMAIL PROTECTED] 10/04/02 03:21PM >>>
Thanks Dale, Michael and Ron.
The "default null" is what I thought about first and it almost does the
job. It's just that it appears like there is a default value which is
null. If I don't include the DEFAULT clause, it does nothing. I think
I'll have to live with the "default null".
At 08:29 2002-10-04 -0800, you wrote:
>Louis,
> I believe it is the ALTER TABLE command.
>ALTER TABLE name
>MODIFY ( column datatype);
>match the column name and the datatype but do not include the DEFAULT
>clause .
>Ron
>ROR mª¿ªm
>
> >>> [EMAIL PROTECTED] 10/04/02 11:28AM >>>
>Anyone knows how to get rid of a column default value ? I rtfm and
>search
>metalink with no luck.
Louis Brouillette
Analyste en informatique (DBA)
Universite du Quebec a Trois-Rivieres
Tel: (819) 376-5011 ext. 2435
Email: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Louis BROUILLETTE
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
