5

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?

4

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
  • nice. Maybe a trim around the substr is useful? and, why use a = NULL if you drop it later? – Wouter Sep 5 '14 at 9:57
  • @Wouter Maybe setting the old column to null is intended to reduce memory consumption during the transition? – Christian Semrau Jul 15 '15 at 15:55
0

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.

0

For Oracle recommend using their specific DBMS package for this:

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.