0

We are doing an Oracle Database Unicode conversion from WE8ISO8859P15 single Byte characterset to AL32UTF8 is Multibyte Characterset. It seems our software vendor mixed VARCHAR2(CHAR and Byte) in the data model. After the datamodel has been moved to a Unicode Oracle database (datapump metadata only), the migration tool uses the following query to decide if it should go from varchar2 to CLOB expecting varchar2(byte).

select 'ALTER TABLE '||table_name||' MODIFY '||column_name||' LONG;'||chr(13)||chr(10)||'ALTER TABLE '||table_name||' MODIFY '||column_name||' CLOB;' from user_tab_columns where (data_type='CHAR' or data_type='VARCHAR2') and data_length>2000 and table_name in (select table_name from user_tables);

The problem is that varchar2 (char) has been increased automatically by x4 by the Oracle Database reaching the limit of 4000 bytes in some cases. The datalength of 2000 seem to be odd. Is a datalength of 2000 a good limit to go to clob? The Oracle Database parameter MAX_STRING_SIZE can be used to increase the limit of 4000 bytes for varchar2 but this could have other side effects.

How should varchar2 (char) be handled extending the query above?

r0tt
  • 379
  • 3
  • 20
  • problem with conversion from varchar2 to club is that this is not transparant for the application. The applications needs to be changed for it. –  Aug 30 '21 at 06:41
  • Using MAX_STRING_SIZE to go to 32k varchar2 has much less side effect than changing a column from varchar2 to CLOB (or worse LONG, as the first statement in your case does). And, if the tables contain data, you cannot just alter table and modify column type. – gsalem Aug 30 '21 at 14:38
  • The 4000 limit is in bytes. With a single byte character that is 4000 characters, with a double byte character set (2bytes per character) this reduces to 2000 characters. With version 12c and later and if the DBA set `max_string_size=extended` (unfortunately most it seems have not) this expands to 32767bytes. If the resulting byte semantics exceed those limits then is not just a good idea go to CLOB, but mandatory to do so. Do not under any circumstances go to LONG, you will regret it. – Belayer Aug 30 '21 at 19:26

0 Answers0