Re: 10g - Column Conversion: Varchar2 to CLOB

From: <fitzjarrell_at_cox.net>
Date: Tue, 31 Jul 2007 07:48:39 -0700
Message-ID: <1185893319.240757.113500@z28g2000prd.googlegroups.com>

On Jul 30, 4:02 pm, "Craig" <Cr..._at_mailinator.com> wrote:
> 10g:
>
> Say I have EMPLOYEES table with column Skills as varchar2
> I need to convert Skills to NCLOB, this is the "right way" to do it ?
>
> alter table EMPLOYEES add skills_clob nclob ;
> update EMPLOYEES set skills_clob = skills ;
> commit;
>
> alter table EMPLOYEES drop column skills;
> alter table EMPLOYEES rename column skills_clob to skills;
>
> thanks

Your method looks good. Remember you'll lose any indexes you built on the SKILLS column when you drop it from the table; of course they won't be applicable once you change the datatype to CLOB/NCLOB from VARCHAR2. You do, however, get the LOB index by default:

SQL> create table t(testcol varchar2(40), testnum number);

Table created.

SQL> insert into t values ('abcdefgijkl', 17);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t add testclob nclob;

Table altered.

SQL> update t set testclob = testcol;

1 row updated.

SQL> commit;

Commit complete.

SQL> create index testindx on t(testcol, testnum);

Index created.

SQL> create index testindx2 on t(testcol);

Index created.

SQL> select index_name, table_name, status   2 from user_indexes;

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
TESTINDX2                      T                              VALID
TESTINDX                       T                              VALID
SYS_IL0000029476C00003$$       T                              VALID

SQL> alter table t drop column testcol;

Table altered.

SQL> select index_name, table_name, status   2 from user_indexes;

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
SYS_IL0000029476C00002$$       T                              VALID

SQL> alter table t rename column testclob to testcol;

Table altered.

SQL> select index_name, table_name, status   2 from user_indexes;

INDEX_NAME                     TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
SYS_IL0000029476C00002$$       T                              VALID

SQL> create index testindx on t(testcol, testnum); create index testindx on t(testcol, testnum)

                           *

ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

SQL> David Fitzjarrell Received on Tue Jul 31 2007 - 09:48:39 CDT