Oracle default column values

Hello everyone. I need to know (and can't find anywhere) how the DEFAULT option on columns works. My problem is I have a table such as:

CREATE TABLE table1 (
id number(6) not null,
field1 varchar(155) not null,
field2 varchar(155) DEFAULT 'something' not null);

Now if I do an insert like this:

INSERT INTO table1 (id,field1) VALUES ('5','somethingelse');

I get an error field2 cannot be null. How can I make the field2 default value be used when a value isn't provided? Thank you!

Everything fine with ur query....
I tried the same .....
Check it out...
SQL> CREATE TABLE table1 (
2 id number(6) not null,
3 field1 varchar(155) not null,
4 field2 varchar(155) DEFAULT 'something' not null);

Table created.

SQL> INSERT INTO table1 (id,field1) VALUES ('5','somethingelse');

1 row created.

SQL> select * from table1
2 ;

ID
----------
FIELD1
--------------------------------------------------------------------------------
FIELD2
--------------------------------------------------------------------------------
5
somethingelse
something

How strange. What version of Oracle? I'm using 8.1.7 but I definitely get an error - it won't let me add a record if the field is mandatory and a value isn't specified, even when it has a default value...

I'm having ORACLE10g db ..

I'm also on 8.1.7 (on NT) and your example works just fine.

OK, I think I know what's going on. I can do it in some tables but not in others. The problem I think is the default value in some cases wasn't defined as 'something', but something instead (no apostrophes). Then it won't let me insert rows if the value isn't specified because the default value is invalid. Thankfully I have to completely overhaul the (inherited) database so I'll be able to repair it. Thanks everyone for your help.