ORA-00942: table or view does not exist Errors on FSI_BI_SETUP_TABLE_51 and FCT_FCST_EXCHANGE_RATES_51 Running Upgrade from ALMBI 5.6 to 6.0 (Doc ID 1498259.1)

Last updated on OCTOBER 12, 2012

Applies to:

Oracle Financial Services Asset Liability Management Analytics - Version 6.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) - Version 7.x
Oracle Financial Services Analytical Applications (OFSAA)
ALM BI

Symptoms

On Oracle Financial Services Asset Liability Management Analytics (ALMBI) 6.0 when attempting to upgrade from ALMBI 5.6, the following error occurs.

ERROR
[Thu 04/10/2012 03:10:54] [OFSALM] [DEBUG] - ===>Executing
file:/u01/OFSDV1/ofsaa/scripts/atomic/insert/post_dm_model_ins_ALMBI_522_56_SP.sql
[Thu 04/10/2012 03:10:54] [OFSALM] [ERROR] - Query:'INSERT INTO
FSI_BI_SETUP_TABLE(target_table_name, target_col_name, member_col_name,
source_dim_table_name, source_column_name, skey_column_name, join_required)
select DISTINCT target_table_name, target_col_name, member_col_name,
source_dim_table_name, source_column_name, skey_column_name, join_required
from fsi_bi_setup_table_51'
  Error:ORA-00942: table or view does not exist

[Thu 04/10/2012 03:10:54] [OFSALM] [ERROR] - Query:'INSERT INTO
FCT_FCST_EXCHANGE_RATES
(n_proc_scen_skey,
n_run_skey,
n_as_of_date_skey,
v_from_currency,
v_to_currency,
n_exchange_rate,
n_bucket_skey,
v_rate_data_source_cd)
SELECT n_proc_scen_skey,
n_run_skey,
n_as_of_date_skey,
v_from_currency,
v_to_currency,
n_exchange_rate,
n_bucket_skey,
'Default'
FROM FCT_FCST_EXCHANGE_RATES_51'
  Error:ORA-00942: table or view does not exist

[Tue 02/10/2012 08:11:11] [OFSALM] [ERROR] - Query:'DECLARE
TYPE typ_col_name IS table of varchar2(30) index by PLS_INTEGER;
l_col_name typ_col_name;
l_comm_col varchar2(32767);
BEGIN
FOR l_bkp_tbl_rec IN (SELECT table_name, BACKUP_TABLE_NAME
from backup_table_map) LOOP
BEGIN
l_col_name.delete;
execute immediate ' SELECT BKP.column_name FROM user_tab_columns ACT, user_tab_columns BKP
WHERE ACT.table_name = :1 AND BKP.table_name = :2 AND ACT.column_name = BKP.column_name' bulk
collect
into l_col_name
using l_bkp_tbl_rec.TABLE_NAME, l_bkp_tbl_rec.BACKUP_TABLE_NAME;
END;
l_comm_col := '';
FOR i in 1 .. l_col_name.LAST LOOP
l_comm_col := l_comm_col || l_col_name(i) || ',';
-- dbms_output.put_line('Common COlumns ' || l_comm_col);
END LOOP;
l_comm_col := RTRIM(l_comm_col, ',');
execute immediate ' INSERT INTO ' || l_bkp_tbl_rec.TABLE_NAME || '( ' ||
l_comm_col || ') SELECT ' || l_comm_col || ' FROM ' ||
l_bkp_tbl_rec.BACKUP_TABLE_NAME;
COMMIT ;
END LOOP;
END;'
 Error:ORA-06550: line 7, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 6, column 23:
PL/SQL: SQL Statement ignored
ORA-06550: line 14, column 7:
PLS-00364: loop index variable 'L_BKP_TBL_REC' use is invalid
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 22, column 38:
PLS-00364: loop index variable 'L_BKP_TBL_REC' use is invalid
ORA-06550: line 22, column 1:
PL/SQL: Statement ignored

[Tue 02/10/2012 08:11:11] [OFSALM] [ERROR] - Query:'DROP TABLE backup_table_map'
 Error:ORA-00942: table or view does not exist

[Tue 02/10/2012 08:11:12] [OFSALM] [DEBUG] - ===># of errors in file=4
[Tue 02/10/2012 08:11:12] [OFSALM] [DEBUG] - ===>Number of lines Read: 4

This script should not have been executed by the ALMBI upgrade kit unless upgrading from 5.2.2 version. The ALMBI 6.0 upgrade log clearly identifies version as 5.6:

[Thu 04/10/2012 03:03:04] [OFSALM] [DEBUG] - Existing version : 5.6So will
try to upgrade...
[Thu 04/10/2012 03:03:17] [OFSALM] [ INFO] - Reading values....

As per the ALMBI 6.0 Install guide:

12.) Upgrade from ALMBI v5.2.2/5.6 to 6.0
Execute the below script mentioned in atomic schema.
dm_pre_model_almbi_60.sql

13.) Upgrade from ALMBI v5.2.2 to 6.0
Execute below scripts in the order mentioned in atomic schema.
a) pre_model_ddl_ALMBI_522_56_SP.sql
b) pre_model_ins_ALMBI_522_56_SP.sql
c) dm_pre_model_almbi_60.sql

The pre_model_ddl_ALMBI_522_56_SP.sql is not run unless you are upgrading
from 5.2.2, therefore the post_dm_model_ins_ALMBI_522_56_SP.sql should also
not be run.

STEPS
The issue can be reproduced at will with the following steps:
1. Run ALMBI 6.0 installer on 5.6 instance.

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