varchar to clob

[dinshak]

need to change varchar field that has data to clob?

 

[IanWaterman]

use the function to_clob()

Ian

 

[SantaMufasa]

Here is the conventional wisdom on "changing" a VARCHAR2 column to CLOB:

SQL> desc dinshak
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 NAME                                      NOT NULL VARCHAR2(50)

SQL> select * from dinshak;

NAME
--------------------------------------------------
North America
South America
Africa / Middle East
Asia
Europe
Australia

6 rows selected.

SQL> alter table dinshak add x clob;

Table altered.

SQL> update dinshak set x = name;

6 rows updated.

SQL> alter table dinshak drop column name;

Table altered.

SQL> alter table dinshak rename column x to name;

Table altered.

SQL> select * from dinshak;

NAME
--------------------------------------------------------
North America
South America
Africa / Middle East
Asia
Europe
Australia

6 rows selected.

SQL> desc dinshak
 Name                                      Null?    Type
 ----------------------------------------- -------- ----
 NAME                                               CLOB

Let us know your reactions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dinshak]

Hi,
Thank you for replying.

I should have mentioned earlier that the steps are successful till SQL> update dinshak set x = name;
it shows successfuly completed but when I go to view edit comments to look at the data it is empty.

 

[SantaMufasa]

Sounds like you viewed your changes from another session, which requires your doing a COMMIT in your original session. Could that explain the (mis)behaviour?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dinshak]

can you be more specific as i am new to this.

 

[dinshak]

i am trying to view it from enterprise manager console

 

[dinshak]

where exactly should i use the commit statement. i used it after update but still the clob fields are empty.

 

[SantaMufasa]

Sorry for the delay, dinshak (I had to do some actual work for a few minutes. <grin>)

where exactly should i use the commit statement. i used it after update but still the clob fields are empty.

That is puzzling...you should be able to see the new CLOB contents immediately from the session where you did the update, and from any other venue/session following the COMMIT.


If (from SQL*Plus or some other SQL-executing application) you do a "SELECT COUNT(<varchar2 column name>) FROM <table_name>;" what is the numeric result?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]