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 :: oracle problem when using ALTER TABLE to set DEFAULT NOT NULL : ORA-30649
By: Nono Great Britain  Date: 11/05/2008 07:51:23  English  Points: 20 Status: Answered
Quality : Excellent
Hello,

I'm seeing this :

SQL> alter table dept2 modify loc not null default 'Y'; alter table dept2 modify loc not null default 'Y' * ERREUR à la ligne 1 : ORA-30649: mot-clé DIRECTORY absent SQL> alter table dept2 modify loc varchar2(13) not null default 'Y'; alter table dept2 modify loc varchar2(13) not null default 'Y' * ERREUR à la ligne 1 : ORA-30649: mot-clé DIRECTORY absent



Why ? What am I doing wrong ? And what is the "DIRECTORY" keyword ?
By: VGR Date: 11/05/2008 08:25:46 English  Type : Answer
Hello,

In fact your problem has three aspects :
1) Oracle seems stricter than other SQL-99 compliant RDBMS (MySql not to name it ;-) in the order of elements in clauses. The SQL-99 standard states indeed that (in chapter 11.5) a default clause is :

<default clause> ::= DEFAULT <default option> <default option> ::= <literal> | <datetime value function> | USER | CURRENT_USER | CURRENT_ROLE | SESSION_USER | SYSTEM_USER | CURRENT_PATH | <implicitly typed value specification>



And Oracle interprets this litterarily by enforcing that the NOT NULL part should be AFTER the DEFAULT one.

If you try this, it'll work right out :

SQL> alter table dept2 modify loc varchar2(13) default 'Y' not null;



2) Oracle diverges one more time from the standard with its DIRECTORY story : DIRECTORY is completely absent from the SQL-99 spec.

3) One more time, the error message Oracle spits out is completely inadequate for the situation...

Best regards

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 257.678990 milliseconds

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