What's wrong with adding LOBs to an Oracle table?

Question

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.

Answer 1

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