Last updated on SEPTEMBER 07, 2017

Applies to:

Goal

OMIPM periodically runs IPLS_DIMENSION.populateAllDIMKEY as a DBMS_JOB so it can populate the necessary internal dimkey values. The user has hierarchies sometimes up to 8 levels deep which causes the DIMKEY code to require more than the RAW(250) space allocated on the DIMENSION_DISPLAY, territory, media, language, format and right_type tables table. When running the following (and when the related IPM job runs), it gives an error.

As a result of the failure, dimension attributes (territory, media, format) which exist on the DRO license do not display in the DRO royalty tab.

Scenario:
1. Create a hierarchy 10+ levels deep
2. Run the following

BEGIN
ipls_cleanup.resetallrdhierarchy;
IPLS_DIMENSION.populateAllDIMKEY;
ipls_dimension.updateAllDimDIMKEY_dimkey;
Ipls_hierarchy.resolveDimension_col;
commit;
END;
/
Error report:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "DWOMIPM.IPLS_DIMENSION", line 46
ORA-06512: at line 5
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:

Please Confirm that following the Recommended Solution is acceptable for production:
-- expand column size in tables
alter table dimension_display
modify( "DIMKEY" RAW(2000))
modify( "TERR_DIMKEY" RAW(2000))
modify( "MEDIA_DIMKEY" RAW(2000))
modify( "LANG_DIMKEY" RAW(2000))
modify( "FORMAT_DIMKEY" RAW(2000))
/

alter table territory
modify (dimkey raw(2000))
/

alter table master_media
modify (dimkey raw(2000))
/

alter table master_language
modify (dimkey raw(2000))
/

alter table master_format
modify (dimkey raw(2000))
/

alter table master_right_Type
modify (dimkey raw(2000))
/

-- refresh tables
BEGIN
ipls_cleanup.resetallrdhierarchy;
IPLS_DIMENSION.populateAllDIMKEY;
ipls_dimension.updateAllDimDIMKEY_dimkey;
Ipls_hierarchy.resolveDimension_col;
commit;
END;
/
 

Solution