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

Answer 2

alter table t add a_column clob;
alter table t add a_column_with_max_size varchar2(1234); --max 4000

Answer 3

If you never want more than 2048 characters in that column, don't use a CLOB, use VARCHAR2(2048). VARCHAR2 is good for up to 4000 characters; only use CLOB if you may need more than that.