We are trying to load a huge organization hierarchy into TRIRIGA (16 levels). As I understand it, for the Hierarchy BOs, such as Geography, Location, or Organization, there is a field called triPathTX, which holds the full path of the record. In most cases, the length of triPathTX is 1000. So, in our ETL transformation, we have truncated the triNameTX of the organization, in order to respect the length max: 1000. But, unfortunately, we have this error in the log of DataConnect:
com.tririga.platform.persistence.PersistenceException: Error executing sql: Sql[SQL ... ORA-12899: value too large for column "TRIDATA"."IBS_SPEC"."OBJECT_PATH" (actual: 504, maximum: 500)
Obviously, the length of the organization path is greater than the length of “IBS_SPEC.OBJECT_PATH” which is configured as 500. Is this a bug? For me, this column should have the same length as triPathTX. Can we alter the table IBS_SPEC with SQL? Are there any other tables that should be altered at the same time? Thanks in advance for your help!
Please log a PMR for this issue. Our applications have SYS_ORGNAME and TRIPATHTX fields in a T_Table set to 1000, but OBJECT_PATH is set to 500. Before we can comment as to whether or not we can alter the OBJECT_PATH, we need to review the impact to the indexes that we have in place on this table. In the interim, can you shorten the path to under 500 characters?
We have confirmed that it’s safe to update the column size of OBJECT_PATH to 1000 given that your db_block_size was set to 8k. If your db_block_size was set to 2k, for example, the field could not have been extended past 758 or we would have seen issues with the index. Below is a table showing the key length and block size limitations. As a general guideline, though, we recommend keeping the path limit as short as possible and the hierarchy tree levels as shallow as possible for best performance.