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

We now celebrate our 5 years of existence and break our frequentation records... Here are the stats of those 5 years.

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

 Add This Article To:
 del.icio.usDel.icio.us  diggDigg  googleGoogle  spurlSpurl
 blinkBlink  wongWong  simpySimpy  yahooY! MyWeb 
EContact
browser fav
page generated in 4550.956960 milliseconds

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