Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: last column in a table is added with a DEFAULT
It is impossible to add a NOT NULL column that *doesn't* have a DEFAULT to a table (unless the table is empty). The error you get is "ORA-01758: table must be empty to add mandatory (NOT NULL) column".
But that's a logical problem, not a bug. Is there any chance the dev is confusing this problem with something else?
Cheers,
-Roy
Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
Sent: Thursday, August 07, 2003 12:30 PM
To: Multiple recipients of list ORACLE-L
See the test below:
YPD_OWNER_at_rex-SQL> create table t1 (c1 number);
Table created.
YPD_OWNER_at_rex-SQL> insert into t1 (c1) values (1);
1 row created.
YPD_OWNER_at_rex-SQL> insert into t1 (c1) values (2);
1 row created.
YPD_OWNER_at_rex-SQL> commit;
Commit complete.
YPD_OWNER_at_rex-SQL> select * from t1;
C1
1 2
YPD_OWNER_at_rex-SQL> alter table t1 add (c2 number default 999);
Table altered.
YPD_OWNER_at_rex-SQL> select * from t1;
C1 C2
---------- ----------
1 999 2 999
-----Original Message-----
Sent: Thursday, August 07, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L
I understand that part. What the programmer is saying that you can not add the last column to a table with a default value. Does that sound reasonable?
thanks, Raj.
David.
-----Original Message-----
Sent: Thursday, August 07, 2003 1:25 PM
To: Multiple recipients of list ORACLE-L
as soon as you add a column all depending code goes invalid, the dependency checking process doesn't discriminate about the default value. Raj
-----Original Message-----
Sent: Thursday, August 07, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Has anybody ever heard of this? I have a developer saying this is an oracle bug. It caused some packages to go invalid.
"The error seems to be related to a bug in oracle caused when the last
column in a table is added with a default."
David Ehresmann
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ehresmann, David
INET: David.Ehresmann_at_ps.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (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).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (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). Received on Fri Aug 08 2003 - 04:09:23 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message