PFT 8.0.7.4 Upgrade Gets 'Error:ORA-00922: missing or invalid option' on VW_EXCHANGE_RATES_LIFETIME with 11gR2 Database (Doc ID 2728553.1)

Last updated on MAY 03, 2023

Applies to:

Symptoms

When installing Oracle Financial Services Profitability Management (PFT) 8.0.7.4 in an environment with an Oracle Database version 11gR2, you get the following error in the OFS_PFT_patch_installation.log:

[02-10-20 09:13:17,946 CDT AM] [INFO ] Executing file:/app/OFSPFT/scripts/atomic/create/VW_EXCHANGE_RATES_LIFETIME.sql
[02-10-20 09:13:17,947 CDT AM] [ERROR] Query:'CREATE OR REPLACE EDITIONABLE VIEW VW_EXCHANGE_RATES_LIFETIME (FIC_MIS_DATE, V_FROM_CCY_CODE, V_TO_CCY_CODE, N_EXCHANGE_RATE, IS_FROM_HISTORY) AS WITH WVW_HISTORY AS
(SELECT fic_mis_date,
v_from_ccy_code,
v_to_ccy_code,
n_exchange_rate
FROM fsi_exchange_rates
WHERE n_run_skey =-1
AND v_rate_data_source_cd = (select v_component_value from setup_master where v_component_code='DEFAULT_FX_RATE_SRC')
AND n_tenor = 0
),
WVW_FUTURE AS
(SELECT fic_mis_date,
v_from_ccy_code,
v_to_ccy_code,
n_exchange_rate
FROM stg_forward_exchg_rates
WHERE fic_mis_date >
(SELECT MAX(fic_mis_date) AS MAX_AS_OF_DATE FROM WVW_HISTORY
)
AND v_rate_data_source_cd = (select v_component_value from setup_master where v_component_code='DEFAULT_FX_RATE_SRC')
AND n_tenor = 0
),
WVW_LIFETIME AS
(SELECT fic_mis_date,
v_from_ccy_code,
v_to_ccy_code,
n_exchange_rate,
'Y' AS IS_FROM_HISTORY
FROM WVW_HISTORY
UNION ALL
SELECT fic_mis_date,
v_from_ccy_code,
v_to_ccy_code,
n_exchange_rate,
'N' AS IS_FROM_HISTORY
FROM WVW_FUTURE
)
SELECT FIC_MIS_DATE,V_FROM_CCY_CODE,V_TO_CCY_CODE,N_EXCHANGE_RATE,IS_FROM_HISTORY FROM WVW_LIFETIME'
Error:ORA-00922: missing or invalid option

[02-10-20 09:13:17,947 CDT AM] [INFO ] # of errors in file=1
[02-10-20 09:13:17,947 CDT AM] [INFO ] # of statements executed: 0

You do not know the impact of this error.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.