ORACLE Character set AL32UTF8 is not supported with Clarity.

Document ID : KB000023269
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Applies to: all Clarity versions prior to 12.1.3

Using Character set AL32UTF8 leads to problems to read/write BLOB data and is NOT supported with Clarity.until version 12.1.3 and v13

Data (ETC and Actuals) gets corrupted after a save. Values are set to null/corrupted.

Easiest way to reproduce is to use OWB/MSP to modify the values, but modifying ETCs on Assignments in Clarity can cause the same issue.

Steps to Reproduce:

  1. Open a project with OWB/MSP
  2. Note the ETC and Actuals for a resource, assigned to a task
  3. Modify the ETC value
  4. Save within OWB/MSP
  5. Close OWB/MSP
  6. Reopen the project in OWB/MSP from Clarity
  7. Note that data from ETC and Actuals is null/wrong

This problem may appear with some projects each time when saving, and not appear with other projects.

If you have 1 task with two assignments, it may happen with the first, but the second assignment can save correctly. It will depend of the size of the field, so it may also be random.

Technical Details:

The PREXTENSION field in the prassignment table holds the practcurve (Actuals) and prestcurve (Estimates) data in a BLOB (Binary Large Object) data type field. The PREXTENSION field doesn't show any bind data after reopening the project.

What we see in the logs is that the data from the client is received appropriately but when the update occurs a last value of null is logged for the PREXTENSION field.

The issue is due to the size of the BLOB.

The AL32UTF8 character set is imposing a limit of 4000 bytes, so the larger values are returning nulls, and Actuals and ETCs are zeroing out.

Solution:

The database needs to be restored on UTF8 character set. The DBA should perform the following steps:

  1. Full export of the database
  2. Drop database
  3. Recreate database with the correct character set
  4. Full import of database with correct character set UTF8
  5. Run SELECT * FROM NLS_DATABASE_PARAMETERS to confirm the character set

It is recommended to do first the change of character set on a test environment and test your data before implementing the solution on production.

AL32UTF8 is supported on 12.1.3 and all 13 versions

Keywords: CLARITYKB, AL32UTF8, Actuals.