How to replace a CLOB column by a varchar2(4000) column?

Question

The following code seems to do the job, but I doubt, that it is robust enough.

SET serveroutput on 

DECLARE
    v_isclob PLS_INTEGER;
    v_cmd    VARCHAR2(4000);
BEGIN
    SELECT CASE when exists (select * from USER_TAB_COLUMNS where COLUMN_NAME = 'Column_to_modify' AND TABLE_NAME = 'MyTable' AND DATA_TYPE = 'CLOB') then 1 else 0 end into v_isclob FROM DUAL;
    IF v_isclob > 0 THEN
        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE MyTable RENAME COLUMN Column_to_modify  TO XXX';

            EXECUTE IMMEDIATE 'ALTER TABLE MyTable ADD Column_to_modify varchar2(4000)';

            EXECUTE IMMEDIATE 'UPDATE MyTable SET Column_to_modify = SUBSTR(XXX, 1, 4000)';

            EXECUTE IMMEDIATE 'ALTER TABLE MyTable DROP COLUMN XXX';
            dbms_output.put_line ('++    Column_to_modify modified to varchar2(4000)');
        EXCEPTION WHEN OTHERS THen
            dbms_output.put_line ('##### Error while modifying Column_to_modify');
        END;
    else
        dbms_output.put_line ('     Column_to_modify is varchar(4000)');
    END IF;
END;
/

How can I improve the code?

Answer 1

I would suggest adding a temp column rather than renaming the existing one. In Oracle 10g I created a test table:

CREATE TABLE  "TEST" 
   (    "A" CLOB
   )

I then inserted data so that the results of length calls are as follows:

select length(a) from test

Result:

"LENGTH(A)"
12308
12308
12308
12308
12308

I then altered the table as follows:

alter table "TEST" modify
("A" VARCHAR2(4000) NOT NULL)
/   

As expected receive the following error: ORA-22859: invalid modification of columns

Change the column using the following script:

ALTER TABLE TEST ADD temp VARCHAR2(4000);
UPDATE TEST
   SET temp = DBMS_LOB.SUBSTR (a, 4000),
       a = NULL;

ALTER TABLE TEST DROP COLUMN A;
ALTER TABLE TEST RENAME COLUMN temp TO A;

Reissue the length sql:

"LENGTH(A)"
4000
4000
4000
4000
4000

Answer 2

The code doesn't handle to presence of an existing column named XXX. You might consider at least making the name a bit more obscure. Perhaps a quoted string of 30 mixed case letters and numbers. Even better you could handle the name exception and try a different name.

Answer 3

For Oracle recommend using their specific DBMS package for this:

• Performing Online Redefinition with DBMS_REDEFINITION

If using the more traditional method when would suggest the following to preserve the order of columns.

 ALTER TABLE TEST ADD temp VARCHAR2(4000);
 UPDATE TEST SET temp = DBMS_LOB.SUBSTR (a, 4000), a = NULL;
 COMMIT;
 ALTER TABLE TEST MODIFY A VARCHAR2(4000);
 UPDATE TEST SET A = temp;
 COMMIT;
 ALTER TABLE TEST DROP COLUMN temp;