Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    26

    Unanswered: Alter Table/Alter Column - No Worky

    I used Oracle Enterprise Manager to set default values for a table column (but it didn't seem to work) Now I am trying to remove the default values setting and I get the error ORA-00936 missing expression.

    When I use the 'Show SQL' feature, it is using the command:
    ALTER TABLE "MYUSER"."MYTABLE" MODIFY("MYCOLUMN" DEFAULT)

    Can anyone help me get the default values OUT of the default value field in Ent. Mgr Console?

    Thanks in advance,

    John

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Just Omit the DEFAULT Clause
    in the statement

  3. #3
    Join Date
    Oct 2003
    Posts
    26
    Originally posted by shelva
    Just Omit the DEFAULT Clause
    in the statement
    Okay, the table was altered but the value still shows up in the EM Console GUI (in the default value column).

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Then just recreate the table!!!
    i.e

    1. create table t1_temp as select * from t1;

    2. drop table t1;

    3. Rename table t1_temp to t1;

    May be this will work;
    make sure that constraints are explicitly added after renaming

  5. #5
    Join Date
    Dec 2003
    Posts
    74

    Angry

    i have looked all over the web and the syntax is

    ALTER TABLE TABLENAME alter COLUMN COLUMN DROP DEFAULT

    but this does not seem to work. I have tried this in an 8i and 9i database????

  6. #6
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Actual syntax is
    alter table <table_name> modify (<column_name> default <default_value>);

    Eg:
    1. alter table t1 modify (a default null);
    (if default in column a is to be removed)

    2. alter table t1 modify (a default -10);
    (if default in column a is to be set or changed to -10)
    Oracle can do wonders !

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Simply do this:

    ALTER TABLE table_name MODIFY column_name DEFAULT NULL;

    NULL is the "default default" for all columns!

  8. #8
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Great andrew!!
    thats y u r the moderator!!

  9. #9
    Join Date
    Oct 2003
    Posts
    26
    Originally posted by shelva
    Great andrew!!
    thats y u r the moderator!!
    Thank you all for your assistance...

    I was confused because the Enterprise Manager Console still showed values in the default value field (now it says NULL) but it had nothing before I started adding stuff.

    Thanks again for your help.

    Happy Holidays!!! :-)

    John

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes - now I look deeper I see that USER_TAB_COLUMNS shows 'null' as the default in this case, but is trully null normally. Presumably it makes no difference, but it is odd and I guess you'd have to drop and recreate the column to get rid of it if you really felt the need.

  11. #11
    Join Date
    Oct 2003
    Posts
    26
    Originally posted by andrewst
    Yes - now I look deeper I see that USER_TAB_COLUMNS shows 'null' as the default in this case, but is trully null normally. Presumably it makes no difference, but it is odd and I guess you'd have to drop and recreate the column to get rid of it if you really felt the need.
    Andrew, I don't feel the need... haha... I just want to make sure it doesn't place a default value... Still I think that EMC is acting a bit quirky in that I started to follow Shelva's advice and just recreate the table using a temp table, but after creating it (and populating it) EMC showed nothing in the 'Rows' column for the temp table even though there were hundreds of rows...

    I'm just trying to figure this out.. haha

    Thanks again for your help... this is a great site!

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •