CODEWITHSUNDEEP
oracle-database
Tgr
Tgr

Reputation: 28170

Add NOT NULL LOB column without default in Oracle

I am trying to add a CLOB column to a table (change a VARCHAR to a CLOB actually, but it only seems possible by adding a new column, copying and dropping the old). The column should be NOT NULL without a default value (and the table is not empty). How can I achieve this?

My original idea was to create the column with a dummy default, and change that later, but that does not seem possible:

ALTER TABLE foo RENAME COLUMN text TO text_temp;
ALTER TABLE foo ADD (
    text CLOB DEFAULT '*' NOT NULL
);
UPDATE foo SET text = text_temp;
ALTER TABLE foo DROP COLUMN text_temp;
ALTER TABLE foo MODIFY (
    text CLOB NOT NULL
);
-- ORA-22296: invalid ALTER TABLE option for conversion of LONG datatype to LOB

I also tried defining the column as text CLOB and adding the NOT NULL constraint later, but it gave the same error. Is there a way of doing this, short of recreating the whole table?

Upvotes: 1

Views: 8607

Answers (2)

Luke Woodward
Luke Woodward

Reputation: 64959

Does

ALTER TABLE foo MODIFY text DEFAULT NULL;

work for you?

When adding or removing a default value or a NOT NULL constraint, you don't need to specify the datatype of the column.

EDIT: To quote the Oracle documentation on ALTER TABLE:

If a column has a default value, then you can use the DEFAULT clause to change the default to NULL, but you cannot remove the default value completely. If a column has ever had a default value assigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will always display either a default value or NULL.

This should explain why you're seeing a difference in SQL Developer.

However, I don't believe there's a significant difference between specifying DEFAULT NULL for a column and not specifying a default value. In both cases, a null value will be assumed for any column not explicitly given a value in an INSERT statement.

Upvotes: 13

APC
APC

Reputation: 146239

You are declaring the column as NOT NULL when you added it to the table, so you don't need to make it NOT NULL again. I think if you corrected the syntax in the final MODIFY clause you would still get an error, albeit a different one (precise number eludes me right now).

But what you are trying to acheive is definitely possible, with the right syntax :)

SQL> alter table t23 add ctxt clob
  2  /

Table altered.

SQL> update t23 set ctxt = txt
  2  /

2 rows updated.

SQL> alter table t23 modify ctxt not null
  2  /

Table altered.

SQL> alter table t23 drop column txt
  2  /

Table altered.

SQL> alter table t23 rename column ctxt to txt
  2  /

Table altered.

SQL>

Upvotes: 3

Related Questions