Modify VARCHAR2 column to CLOB one

yasuo.honda

Hi, I am maintaining Oracle enhanced adapter for Active Record.

I'd like Oracle database to support modify VARCHAR2 column to CLOB.

Right now, it gets ORA-22858 error.

SQL> create table foo (comments varchar2(255));Table created.SQL> alter table foo modify comments clob;alter table foo modify comments clob                       *ERROR at line 1:ORA-22858: invalid alteration of datatype

I'm using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

I did not have a chance to try 18c yet.

Refer the original issue at Oracle enhanced adapter

https://github.com/rsim/oracle-enhanced/issues/1675

Mike Kutz

You can try 18c on http://LiveSQL.oracle.com

Sven W.

Another way to change a column type is to use DBMS_REDEFINITION. You can even access the table during this operation. Here is an old ask tom thread that explains the activity:  https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:9532881300346956686

Mike Kutz

Another way to change a column type is to use DBMS_REDEFINITION. You can even access the table during this operation. Here is an old ask tom thread that explains the activity:  https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:9532881300346956686

@Sven W.

I'm assuming the GitHub project translates Rails code:

change_column :table_name, :column_name, :text

into SQL code

ALTER TABLE TABLE_NAME MODIFY ( COLUMN_NAME CLOB );

( :text is defined as CLOB somewhere in some config file for Rails)

My opinion on DBMS_REDEFINITION

1. For valid single column modifications, using DBMS_REDEFINITION would be overkill.
2. For multi-column modifications, DBMS_REDEFINITION could be appropriate.
3. (as I understand) Column modifications that are not supported by ALTER TABLE should be done via DBMS_REDEFINITION.

I don't know Rails.  I don't know if that language has a standard way of handling (2).

But, if it did, DBMS_REDEFINITION should be the engine used by that Rails syntax.

Back on topic

As I understand, the extend length VARCHAR2 data type (eg VARCHAR2(32767) in 12c ) uses CLOB behind the scenes.

I don't see why Oracle can't make such VARCHAR2s  into an actual CLOB column.

My $0.02

MK

Sven W.

While I agree this would be nice to have. My priority for it would be pretty low. Considering we can still do it, just with a few more steps.

drop table foo;create table foo (comments varchar2(255));-- add some datainsert into foo (comments) values ('This');insert into foo (comments) values ('is');insert into foo (comments) values ('just');insert into foo (comments) values ('a');insert into foo (comments) values ('Test');-- add a new clob columnalter table foo add (comments_large clob);-- move the data to the clob columnupdate foo set comments_large = comments;-- rename the columnsalter table foo rename column comments to comments_old;alter table foo rename column comments_large to comments;-- hide the old columnalter table foo modify comments_old invisible;-- testselect * from foo;-- note that the old data is still there. We just don't see it immediatly-- Quick demnonstrationupdate foo set comments=upper(comments);select comments, comments_old from foo;

COMMENTS    COMMENTS_OLD

THIS         This

IS               is

JUST         just

A                a

TEST         Test

blessed DBA

This not a idea  , this type questions to be posted in other discussion rather than add in ideas

Sven W.

This not a idea  , this type questions to be posted in other discussion rather than add in ideas

Why do you think

"I'd like Oracle database to support modify VARCHAR2 column to CLOB."

is not a clear proposal for an enhancement idea?

blessed DBA

Why do you think

"I'd like Oracle database to support modify VARCHAR2 column to CLOB."

is not a clear proposal for an enhancement idea?

This is a question or doubt ?

Not a idea ,so i told ,the admin others has some rules we have to follow for question or discussion .Other spaces are available for it . Then it is your wish ..

William Robertson

This is a question or doubt ?

Not a idea ,so i told ,the admin others has some rules we have to follow for question or discussion .Other spaces are available for it . Then it is your wish ..

blessed DBA wrote:This is a question or doubt ?Not a idea ,so i told ,the admin others has some rules we have to follow for question or discussion .Other spaces are available for it . Then it is your wish ..

Not sure what you are saying. It's clearly not a question and nobody has expressed any doubts about anything. The proposal is that Oracle adds support for converting VARCHAR2 columns to CLOBs using a single ALTER TABLE command, which I agree might be nice although not especially life-changing.

yasuo.honda

Thanks for the answers and suggestions. 18c gives me the same error.

Yes "alter table foo modify comments clob" is a sample SQL which will be generated by Rails.

While I understand "DBMS_REDEFINITION" can convert VARCHAR2 column into CLOB. Unfortunately, it is hard for me to implement DBMS_REDEFINITION since multiple statements for this operations are not feasible. Where other RDBMS like MySQL and PostgreSQL can do almost the same thing using one SQL statement.

I'd like to keep my idea open for some moment.

Thanks.

Mike Kutz

Thanks for the answers and suggestions. 18c gives me the same error.

Yes "alter table foo modify comments clob" is a sample SQL which will be generated by Rails.

While I understand "DBMS_REDEFINITION" can convert VARCHAR2 column into CLOB. Unfortunately, it is hard for me to implement DBMS_REDEFINITION since multiple statements for this operations are not feasible. Where other RDBMS like MySQL and PostgreSQL can do almost the same thing using one SQL statement.

I'd like to keep my idea open for some moment.

Thanks.

Yasou,

I'm assuming you want the single rails line to remain synchronous.  DBMS_REDEFINITION would be asynchronous.

In the meantime, if you can detect "I'm translating from VARCHAR2 to CLOB", then you should be able to generate a single SQL statement to do the translation.  (an Anonymous PL/SQL Block is a single SQL statement )

Your DDLs will need to be wrapped with EXECUTE IMMEDIATE.

copying @Sven W.'s example

begin  -- add a new clob column  execute immediate 'alter table foo add (comments_large clob);  -- move the data to the clob column  update foo set comments_large=comments;  -- rename the columns  execute immediate 'alter table foo rename column comments to comments_old';  execute immediate 'alter table foo rename column comments_large to comments';    -- hide the old column  execute immediate 'alter table foo modify comments_old invisible';end;

For a more elaborate code, I recommend you post a question in .

("more elaborate" means: having the ability to handle "LONG to CLOB", "LONG RAW to BLOB", "VARCHAR2 to DATE", etc. using a generic template)

My $0.02

MK