Re: Switching column types from VARCHAR2(4000) to CLOB (and maybe back)

In article <8f8ab0c8.0107231523.1c8b4b6c_at_posting.google.com>, gardion_at_my-deja.com says...
>
>Hello,
>
>I want to change a table type from VARCHAR2(4000) to CLOB since this
>would put less of a limit on the amount someone can enter into a
>database. However, it is not a simple case of
>
>ALTER TABLE sa.TicketDetail Modify (RESOLUTION CLOB);
>

well, in 9i, that responds with "ok, done". Its a 9i feature.

>This responds with
>
>0RA-22858: invalid alteration of datatype
>
>The above ALTER TABLE statement produces an error. In fact the Oracle
>Documentation makes a specific note that you cannot convert from
>another Column type to a LOB (CLOB).Is there any way to increase the
>amount of data for the RESOLUTION Column? Is it possible to copy and
>past from a VARCHAR2 Column to a CLOB?
>
>Any suggestions on how to increase the storage space would be
>appreciated. By the way I am using Oracle 8i r3 (8.1.7) Entreprise
>Edition for Solaris (8).
>
>Joel

You do understand that by converting to a CLOB in 8i, you will most likely have to change the applications that do the inserting? Anything less then 4000 bytes can be inserted normally, above that they must accomidate for the fact that it is a clob (using dbms_lob.read, write, substr for example)

Here is how to conver to and then back from a CLOB in a manner of speaking in 8i:

ops$tkyte_at_ORA8I.WORLD> create table t ( x int primary key, y varchar2(4000) ); Table created.

ops$tkyte_at_ORA8I.WORLD> insert into t select object_id, object_name from all_objects where rownum <= 100;
100 rows created.

Thats our data, now, lets convert it:

ops$tkyte_at_ORA8I.WORLD> create table t_tmp as select rowid rid, y from t; Table created.

ops$tkyte_at_ORA8I.WORLD> alter table t drop column y; Table altered.

ops$tkyte_at_ORA8I.WORLD> alter table t add y clob; Table altered.

ops$tkyte_at_ORA8I.WORLD> update t set y = ( select y from t_tmp where rid = t.rowid );

100 rows updated.

There, our varchar2 is a clob. to put it back, we just go in reverse:

ops$tkyte_at_ORA8I.WORLD> drop table t_tmp; Table dropped.

ops$tkyte_at_ORA8I.WORLD> create table t_tmp as select rowid rid, dbms_lob.substr(y,4000,1) y from t;

Table created.

ops$tkyte_at_ORA8I.WORLD> alter table t drop column y; Table altered.

ops$tkyte_at_ORA8I.WORLD> alter table t add y varchar2(4000); Table altered.

ops$tkyte_at_ORA8I.WORLD> update t set y = ( select y from t_tmp where rid = t.rowid );

100 rows updated.

and now its a varchar2 again.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp