ORA-01747: INVALID USER.TABLE.COLUMN Error Using Stage Dim Master Loader - STGDimDataLoader for OFSAA (Doc ID 1350706.1)

Last updated on OCTOBER 04, 2016

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 7.2.8 to 7.3 [Release 7]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.2
Oracle Financial Services Analytical Applications (OFSAA) - Version 5.x

Symptoms

In OFSAAI 7.2.8, when attempting to run the Stage Dimension Loader Procedure using the Master Table approach to load the ROLLUP SIGNAGE required attribute for the Product dimension, the following error occurs:

ERROR
Error tttORA-01747: invalid user.table.column, table.column, or column specification

The issue can be reproduced at will with the following steps:

1. Populate FSI_DIM_ATTRIBUTE_MAP appropriately:
 
"N_DIMENSION_ID"    "V_STG_TABLE_NAME"    "V_STG_COLUMN_NAME"    "V_ATTRIBUTE_NAME"    "V_
UPDATE_B_CODE_FLAG"
4    "stg_product_master"    "v_common_coa_code"    "COMMON COA ID"    "N"
4    "stg_product_master"    "v_prod_code"    "PRODUCT CODE"    "Y"
4    "stg_product_master"    "v_rollup_signage_code"    "ROLLUP SIGNAGE"    "N"
 
2. Populate STG_PRODUCT_MASTER and enter the v_rollup_signage_code = 1 or 2
 
3. Run the fn_STGDimDataLoader for Dimension = 4
 
4. Check the FSI_MESSAGE_LOG incorrect sql is generated:
 
 merge into DIM_PRODUCTS_ATTR target_table
                       using (
                              select
                              bb.PRODUCT_ID id_column ,5054 attribute_id
                              , cc.ROLLUP_SIGNAGE_CD attr_val
                               from  stg_product_master aa
                              , DIM_PRODUCTS_B bb, FSI_ROLLUP_SIGNAGE_CD cc
                              where
                              aa.v_rollup_signage_code = cc. and  <== blank column from FSI_ROLLUP_SIGNAGE_CD
                               1=1  and aa.v_rollup_signage_code is not null and
                              aa.v_prod_code   = bb.PRODUCT_CODE
                         ) merge_query
                       on (
                        target_table.PRODUCT_ID= merge_query.id_column
                            and
                       target_table.attribute_id = merge_query.attribute_id
                          )
 
                      when matched then update set
                           target_table.DIM_ATTRIBUTE_NUMERIC_MEMBER = attr_val
                       when not matched then
                        insert
                           (
                              target_table.PRODUCT_ID,
                              target_table.attribute_id,
                              DIM_ATTRIBUTE_NUMERIC_MEMBER
                            )
                        values
                            (
                              merge_query.id_column ,
                              merge_query.attribute_id,
                              merge_query.attr_val
                            )

Cause

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