From time to time, you might run into some tasks like converting column data type. Oracle has provided the feature with “alter table” statement with some limitations — for example, you cannot change a column type from clob to varchar2 with it:

SQL> desc jli_0104
 Name               Null?    Type
 ------------------ -------- ------------
IUID                          CLOB

SQL> ALTER TABLE jli_0104 MODIFY iuid varchar2(100);
ALTER TABLE jli_0104 MODIFY iuid varchar2(100)
                            *
ERROR at line 1:
ORA-22859: invalid modification of columns

You will see the ORA-22859 error.

[oracle@joetest~]$oerr ora 22859
22859, 00000, "invalid modification of columns"
// *Cause:   An attempt was made to modify an object, REF, VARRAY, nested
//           table, or LOB column type.
// *Action:  Create a new column of the desired type and copy the current
//           column data to the new type using the appropriate type
//           constructor.

There are a couple of ways to work around this:

1. Create a new table with the new varchar2 column type and port clob data to it using dbms_lob.substr() with “insert into select“. Then drop the old table and rename the new table.
2. Create a new varchar2 column within the table, then port clob data to it using dbms_lob.substr(). Drop the old column and rename the column.

When porting clob data, the size of clob matters because even from 12c onward, the maximum size of varchar2 is 32767 bytes with the parameter MAX_STRING_SIZE set to EXTENDED. And with the default value STANDARD, 4000 bytes is the maximum.

You need to use dbms_lob.getlength(clob_column) to check the length of the column to make sure the size of VARCHAR2 defined can hold data converted.

If you don’t need to convert the whole content of clob column, you can even further reduce the lengh with other functions like regexp_substr, regexp_replace, substr etc.

For example, I know my data has a string with the format “IUID=1.2.3.4.5.1232423.12242323” and I am only interested in the iuid value itself. I can create an interim clob colum to hold a short version of original clob data. Then convert it to the varchar2 type.

alter table jli_0104 add (iuid_clob_short clob);
alter table jli_0104 add (iuid_var varchar2(100));

update jli_0104 set iuid_clob_short=regexp_replace(regexp_substr(IUID, 'IUID=[0123456789.]+'), 'IUID=','');
update jli_0104 set iuid_var=dbms_lob.substr(iuid_clob_short,dbms_lob.GETLENGTH(iuid_clob_short),1);

commit;

alter table jli_0104 drop column iuid_clob_short;

After the convertion, I can easily use equal sign to search without using other functions.

select count(*) from jli_0104 where iuid_var = '1.2.3.4.5.1232423.12242323';

• Oracle