Oracle – What’s wrong with adding LOBs to an Oracle table

alter-tableblobcloboracle

I'm trying to ALTER a table by adding a new CLOB column (on Oracle 10), but it's failing.
Giving me an ORA-01735 error. Problem is I can't find out what in particular is wrong with my query by googling around so I figured I'd ask here just in case.

Anyways my query is:

 ALTER TABLE "MYSCHEMA"."MYTABLE" ADD "ACOLUMN" CLOB(2048);

And get the following error:

SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"

Any ideas?

Thanks.

Best Solution

You can't specify a size for CLOB (use VARCHAR if you want to specify a size):

SQL> alter table t add ("ACOLUMN" CLOB(2048));

alter table t add ("ACOLUMN" CLOB(2048))

ORA-00907: missing right parenthesis

SQL> alter table t add ("ACOLUMN" CLOB);

Table altered

Related Solutions

Sql – Changing Oracle table name

Syntax "RENAME TABLE tab_old TO tab_new" is not correct one.
Correct syntax: "RENAME tab_old TO tab_new".
Word "TABLE" shouldn't be in the statement.

Sql – Altering data type in SQL developer

You can do the following (if column is empty or contains NULLs only)

alter table Orders modify (OrderDate date);

If column is not empty

alter table Orders
  add( new_date_col date );

update Orders
   set new_date_col = to_date(old_date_col, 'DDMMYYYY') // or other correct format
// 'DDMMYYYY' will work if you current number row contains 01012010

alter table orders
 drop column old_date_column;

alter table orders
 rename column new_date_col TO old_date_col;

Best Solution

Related Solutions

Sql – Changing Oracle table name

Sql – Altering data type in SQL developer

Related Question