This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,831 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Modify VARCHAR2 column to CLOB one

yasuo.honda
yasuo.honda Member Posts: 3 Blue Ribbon
edited Apr 16, 2018 8:13AM in Database Ideas - Ideas

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

yasuo.hondaSven W.blessed DBA
4 votes

Active · Last Updated

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,317 Gold Crown
  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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
    Mike Kutz Member Posts: 6,317 Gold Crown

    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.
    Sven W. GermanyMember Posts: 10,562 Gold Crown
    edited Apr 19, 2018 6:15AM

    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
    blessed DBA Member Posts: 218

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

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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?

    William Robertson
  • blessed DBA
    blessed DBA Member Posts: 218

    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
    William Robertson London, UKMember Posts: 9,579 Bronze Crown

    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
    yasuo.honda Member Posts: 3 Blue Ribbon

    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
    Mike Kutz Member Posts: 6,317 Gold Crown
    edited Apr 23, 2018 8:50AM

    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

    Sven W.