1. Oracle Enterprise Data Quality Online Help
  2. Oracle Enterprise Data Quality Applications Help Topics
  3. Working with Long Text

1.20 Working with Long Text

From version 12.2.1.4.1 onwards EDQ supports databases with max_string_size set to EXTENDED, which allows you to create VARCHAR columns with a maximum size of up to 32767 bytes. All the VARCHAR columns in EDQ results tables that previously defaulted to VARCHAR(4000) are now created as VARCHAR(32767). While this allows storage of much longer data, some customer environments encounter issues such as the following:
  • Results writing to VARCHAR(32767) could be significantly slower.
  • VARCHAR columns with a defined length that was greater than 6398 bytes could not be indexed.
Results table indexes are generally used to sort and filter data in the Results Browser. While a lack of results table indexes will not cause issues in batch processing, there are some uses cases where an index is vital for acceptable performance such as:
  • Key columns in staged data lookups.
  • The CODED_VALUE column in match tables.

You could reduce the maximum VARCHAR size by setting the property oracle.max.string.size in the file director.properties. For example: 

oracle.max.string.size = 4000

However, this would mean that it would not be possible to write long data if needed.

To retain acceptable performance in most cases and to allow long data to be written when necessary, EDQ version 12.2.1.4.3 onwards the staged data and snapshot creation mechanisms allow individual columns to be marked as "long". Columns marked as long are created as VARCHAR(32767) whereas other columns are created as VARCHAR(4000).

You can control the default column sizes by setting the following properties in the file director.properties:
  • oracle.default.string.size: Sets the size used for any column not marked as "long". The default value is 4000.
  • oracle.max.string.size: Sets the size used for any column marked as "long". The default value is 32767 for databases with extended strings enabled, and 4000 otherwise.

Note that the long column selection flags are not shown if the values for oracle.default.string.size and oracle.max.string.size are equal. This could happen, for example, with databases that do not have extended strings enabled.