visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: Oracle :: problem in a perfectly valid ALTER TABLE "table must be empty to add mandatory (not null) columns"
By: Bernard France  Date: 20/07/2005 08:54:59  French English  Points: 30 Status: Answered
Quality : Excellent
Salut. J'essaie de faire quelquechose de parfaitement valide et Oracle me jette en disant qu'il ne peut pas le faire !!!

Alter table billy.tbltacc_ll add ( incumb_wscustomerid CHAR(3) DEFAULT 'FAC' NOT NULL, incumb_voice_nr VARCHAR(20) NULL, incumb_voice_type VARCHAR(20) NULL, incumb_offername VARCHAR(50) NOT NULL, dsl_installation VARCHAR(4) DEFAULT 'NONE' NOT NULL );



I don't understand... This is perfectly valid SQL in my humble opinion...

I get the above-mentioned error.

Oracle ne sait pas faire ça ?!?
By: VGR Date: 20/07/2005 09:17:38 English  Type : Answer
Absolutely true.

Oracle requires your table to hold NO DATA AT ALL when adding a "NOT NULL" column to it, whilst MySql would accept it as there was a DEFAULT value provided to fill them in. This is logical IMHO.

One more addition to the crudeness and lack of power of Oracle. By "power" I mean "useful for the user".

Also please notice that Oracle has probably refused also the "NOT NULL DEFAULT 'FAC'" and required that you write "DEFAULT 'FAC' NOT NULL", however illegitimate this may seem.

The solution of your problem may be to add the columns without "DEFAULT 'xxx' NOT NULL", then UPDATE the table to hold the default value 'xxx' (is it probably contains NULL in stead), then ALTER TABLE to set the "DEFAULT 'xxx' NOT NULL" attribute

In fact, the "standard and recommended" way by Oracle Corp. is to DROP YOUR TABLE !!! and reconstruct it with the new attributes in place !!!

it's only three statements after all.

Note that the MySql server would serve you directly, even if with warnings.

There are so many annoying DDL limitations like this one that I wonder why some people stil pay for Oracle while they could get MySql for free...

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 291.489840 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page