HDR: Value Too Large For Column "HCT"."HCT_ST_LOINC_CONCEPTS"."LOINC_FORMULA_TXT" During LOINC Loading (Doc ID 2245628.1)

Last updated on SEPTEMBER 12, 2017

Applies to:

Oracle Healthcare Data Repository - Version and later
Information in this document applies to any platform.


On : version, RIM Bulk Loading

When attempting to load LOINC version 258 the following error is encountered:

ORA-12899: value too large for column "HCT"."HCT_ST_LOINC_CONCEPTS"."LOINC_FORMULA_TXT" (actual: 264, maximum: 255)”.

“A SQL exception was encountered while processing line LINE_NUMBER of file FILE_NAME. ORA-12899: value too large for column "HCT"."HCT_ST_LOINC_CONCEPTS"."LOINC_FORMULA_TXT" (actual: 264, maximum: 255)”.

The defined size of the column “LOINC_FORMULA_TXT” within “HCT_ST_LOINC_CONCEPTS” table is defined to 255 bytes.

That value is too small according to LOINC release notes: https://loinc.org/downloads/files/LOINC_ReleaseNotes.txt

Formal Definition: Contains the formula in human readable form, for calculating the value of any measure that is based on an algebraic or other formula except those for which the component expresses the formula. So Sodium/creatinine does not need a formula, but Free T3 index does.
Action: EXPAND from text (255) to memo
Discussion: The LOINC content developers have been jumping through hoops to make some formulas fit the field limit (255 characters). Truncating or unnecessarily fiddling with formulas to fit the field limit makes things harder than it needs to be for both LOINC staff and LOINC users. Expanding the size of this field will give us the space needed to properly document the formula.
As an example, here is a formula that currently is 472 characters long:

"GT = BetaT * (DT + [TSH]) * (1 + K41 [TBG] + K42 [TBPA]) * [FT4] / (AlphaT * [TSH]) or GT = BetaT * (DT + [TSH]) * [TT4] / (AlphaT * [TSH]) from serum or plasma concentrations of thyrotropin (TSH) and free (FT4) or total (TT4) thyroxine and some constants (AlphaT = Dilution factor for T4: 0.1 l 1; BetaT = Clearance exponent for T4: 1.1e-6 sec-1; K41 = Dissociation constant T4-TBG: 2e10 l/mol; K42 = Dissociation constant T4-TBPA: 2e8 l/mol, DT = EC50 for TSH: 2.75 mU/l)"


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms