ORA-01735: invalid ALTER TABLE option (While trying to drop DEFAULT on a COLUMN)

Question

I was trying to drop DEFAULT constraint on COLUMN sal i.e. salary in customer table in Oracle Database.

SQL statement which I tried was

ALTER TABLE customer ALTER COLUMN sal DROP DEFAULT;

Error message which I received was

ALTER TABLE customer ALTER COLUMN sal DROP DEFAULT
Error report -
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"
*Cause:    
*Action:

Can anyone tell me the mistake which I am doing?

Solution

try this query :

ALTER TABLE customer MODIFY sal DEFAULT NULL;

Comments

[miracle173]

"try this qury" isn't really an explanation why the OP's problem occurs and why your statement should work. I thinl "query" isn't the right term for your statement

[mathguy]

@miracle173 - perhaps you are right in general, but in this case it is obvious even without any explanation that the OP's attempt had syntax mistakes. The responder here provided the correct syntax. What's your objection?

[Rishabh Tiwari]

@batnight your solution has worked. I wanted to figure out is there any way by which we can remove default value which we have set for a column in a table. Setting it back to null is a good way.

[miracle173]

@mathguy e.g. the OP seems to confuse constraints with default values. This can be clarified. A link to the manual that describes the correct syntax could also be useful.