Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

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
share|improve this answer
    
I like it. (+1) –  Leigh Riffel Sep 19 '11 at 17:27
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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