PFT Migration extract_from_source.sh Gets 'Error checking syntax :ORA-00936: missing expression' with 'ON ()' (Doc ID 2316543.1)

Last updated on MAY 03, 2023

Applies to:

Symptoms

When doing a migration from Oracle Financial Services Profitability Management (PFT), Funds Transfer Pricing (FTP), or Asset Liability Management (ALM) 6.1.1 to 8.0, you get the following ORA-00936 error in extract_from_source.log after running extract_from_source.sh:

[2017.09.27 - 08:39:09 AM CDT] Extracting application metadata from Atomic-schema

[2017.09.27 - 08:39:11 AM CDT] Extracting data from : REV_APP_USER_PREFERENCES
[2017.09.27 - 08:39:11 AM CDT] Table [REV_APP_USER_PREFERENCES] has no override(s)
[2017.09.27 - 08:39:12 AM CDT] Removing existing file
[2017.09.27 - 08:39:12 AM CDT] Total records found :28
[2017.09.27 - 08:39:12 AM CDT] Fetching Paginated data - set # 1
[2017.09.27 - 08:39:12 AM CDT] Querying table-data using :SELECT * FROM ( SELECT ROWNUM RNUM, A.* FROM (SELECT ERR_MSG_PER_ITEM, TOTAL_ERR_MSG, ENFORCE_MASS_UNDO, MASTER_MAINTENANCE, YEAR_TYPE, START_MONTH, DEFAULT_APPLICATION_LANGUAGE, HIERARCHY_SIZE, CUSTOM_LEDGER_MIGRATION, CUSTOM_CHARGE_CREDIT, LEDGER_MIGRATION_RATE_BAL_NAME, LEDGER_CHARGE_CREDIT_BAL_NAME, DEFAULT_RELATIONSHIP_MANAGER, NUMBER_OF_ITERATIONS, TO_CHAR(NET_CHANGE_DATE,'YYYYMMDD') NET_CHANGE_DATE, DEFAULT_CUSTOM1_DIMENSION, DEFAULT_CUSTOM2_DIMENSION, LEDGER_FINANCIAL_ELEM_CD, SRC_CONSOLIDATION_CD, DRV_CONSOLIDATION_CD, TAR_CONSOLIDATION_CD, SOURCE_SCENARIO_CD, DRIVER_SCENARIO_CD, OUTPUT_SCENARIO_CD, MAX_CIR_ALLOC_ITER, ENABLE_MASS_UNDO_SA, ENABLE_MASS_UNDO_SB, ENABLE_MASS_UNDO_SAM, CURRENCY_FACTOR_CD, EN_HOL_CAL_ADJUSTMENTS, NAT_HOUSING_PRCING_INDEX, SHOW_RUN_EXE_PARAM, SECURITY_MAP, SYS_ID_NUM, APPID, USER_ID, TO_CHAR(AS_OF_DATE,'YYYYMMDD') AS_OF_DATE, OPTION_COST_PRECISION_FACTOR, DEBUG_LEVEL, DEFAULT_FOLDER_NAME, DEFAULT_GL_ACCOUNT_HIERACHY, INITIAL_CURRENCY_SELECTION, MAX_NO_OF_INSTRUMENTS_OUTPUT, NO_OF_MONTE_CARLO_RATE_PATHS, DEFAULT_ORGUNIT_DIMENSION, DEFAULT_ORGUNIT_HIERARCHY, DEFAULT_PERMISSION, DEFAULT_PRODUCT_DIMENSION, DEFAULT_PRODUCT_HIERARCHY, RANDOM_NUM_GEN_METHOD, INITIAL_SEED_VALUE, LEDGER_MIGRATION_RATE, LEDGER_CHARGE_CREDIT FROM REV_APP_USER_PREFERENCES WHERE APPID='PFT') A WHERE ROWNUM <= 2000) WHERE RNUM >= 1
[2017.09.27 - 08:39:12 AM CDT] Processed 28 record(s) from query
[2017.09.27 - 08:39:12 AM CDT] Error checking syntax :ORA-00936: missing expression
: MERGE INTO REV_APP_USER_PREFERENCES TAB
 USING (
 SELECT 0 ERR_MSG_PER_ITEM
,0 TOTAL_ERR_MSG
, 'N' ENFORCE_MASS_UNDO
, NULL MASTER_MAINTENANCE
, NULL YEAR_TYPE
, NULL START_MONTH
, NULL DEFAULT_APPLICATION_LANGUAGE
, NULL HIERARCHY_SIZE
, NULL CUSTOM_LEDGER_MIGRATION
, NULL CUSTOM_CHARGE_CREDIT
, NULL LEDGER_MIGRATION_RATE_BAL_NAME
, NULL LEDGER_CHARGE_CREDIT_BAL_NAME
, NULL DEFAULT_RELATIONSHIP_MANAGER
.
.
.
, NULL LEDGER_MIGRATION_RATE
, NULL LEDGER_CHARGE_CREDIT FROM DUAL ) PROXY
 ON ()
 WHEN MATCHED THEN UPDATE SET TAB.ERR_MSG_PER_ITEM = PROXY.ERR_MSG_PER_ITEM
 , TAB.TOTAL_ERR_MSG = PROXY.TOTAL_ERR_MSG
 , TAB.ENFORCE_MASS_UNDO = PROXY.ENFORCE_MASS_UNDO
 , TAB.MASTER_MAINTENANCE = PROXY.MASTER_MAINTENANCE
.
.
.
, PROXY.INITIAL_SEED_VALUE
 , PROXY.LEDGER_MIGRATION_RATE
 , PROXY.LEDGER_CHARGE_CREDIT)
[2017.09.27 - 08:39:12 AM CDT] Error checking syntax :ORA-00936: missing expression

The error occurs because the "ON" clause is blank / empty: "ON ()".  As a result, the extract of data from the source environment fails.

Cause

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