ZEK12EkE

1SET serveroutput on 
2
3DECLARE
4    v_isclob PLS_INTEGER;
5    v_cmd    VARCHAR2(4000);
6BEGIN
7    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;
8    IF v_isclob > 0 THEN
9        BEGIN
10            EXECUTE IMMEDIATE 'ALTER TABLE MyTable RENAME COLUMN Column_to_modify  TO XXX';
11
12            EXECUTE IMMEDIATE 'ALTER TABLE MyTable ADD Column_to_modify varchar2(4000)';
13
14            EXECUTE IMMEDIATE 'UPDATE MyTable SET Column_to_modify = SUBSTR(XXX, 1, 4000)';
15
16            EXECUTE IMMEDIATE 'ALTER TABLE MyTable DROP COLUMN XXX';
17            dbms_output.put_line ('++    Column_to_modify modified to varchar2(4000)');
18        EXCEPTION WHEN OTHERS THen
19            dbms_output.put_line ('##### Error while modifying Column_to_modify');
20        END;
21    else
22        dbms_output.put_line ('     Column_to_modify is varchar(4000)');
23    END IF;
24END;
25/
26	
27CREATE TABLE  "TEST" 
28   (    "A" CLOB
29   )
30	
31select length(a) from test
32	
33"LENGTH(A)"
3412308
3512308
3612308
3712308
3812308
39	
40alter table "TEST" modify
41("A" VARCHAR2(4000) NOT NULL)
42/
43	
44ALTER TABLE TEST ADD temp VARCHAR2(4000);
45UPDATE TEST
46   SET temp = DBMS_LOB.SUBSTR (a, 4000),
47       a = NULL;
48
49ALTER TABLE TEST DROP COLUMN A;
50ALTER TABLE TEST RENAME COLUMN temp TO A;
51	
52"LENGTH(A)"
534000
544000
554000
564000
574000