SDE_ORAR1213_Adaptor.SDE_ORA_GLBalanceFact_Full
969151Apr 13 2013 — edited Apr 13 2013Dears,
when im trying run full load in finance analysts via dac, im getting error,
Log file for your reference.
DIRECTOR> VAR_27028 Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].
DIRECTOR> VAR_27028 Use override value [ORA_R1213] for session parameter:[$DBConnection_OLTP].
DIRECTOR> VAR_27028 Use override value [ORA_R1213.DATAWAREHOUSE.SDE_ORAR1213_Adaptor.SDE_ORA_GLBalanceFact_Full.log] for session parameter:[$PMSessionLogFile].
DIRECTOR> VAR_27028 Use override value [1000] for mapping parameter:[$$DATASOURCE_NUM_ID].
DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[mplt_BC_ORA_GL_Balance_Fact.$$FILTER_BY_LEDGER_ID].
DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[mplt_BC_ORA_GL_Balance_Fact.$$FILTER_BY_LEDGER_TYPE].
DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[mplt_BC_ORA_GL_Balance_Fact.$$INITIAL_EXTRACT_DATE].
DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[mplt_BC_ORA_GL_Balance_Fact.$$LAST_EXTRACT_DATE].
DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[mplt_BC_ORA_GL_Balance_Fact.$$LEDGER_ID_LIST].
DIRECTOR> VAR_27027 Use default value [] for mapping parameter:[mplt_BC_ORA_GL_Balance_Fact.$$LEDGER_TYPE_LIST].
DIRECTOR> VAR_27028 Use override value [DEFAULT] for mapping parameter:[$$TENANT_ID].
DIRECTOR> TM_6014 Initializing session [SDE_ORA_GLBalanceFact_Full] at [Fri Apr 12 22:05:36 2013].
DIRECTOR> TM_6683 Repository Name: [Oracle_BI_DW_Base]
DIRECTOR> TM_6684 Server Name: [Oracle_BI_DW_Base_Integration_Service]
DIRECTOR> TM_6686 Folder: [SDE_ORAR1213_Adaptor]
DIRECTOR> TM_6685 Workflow: [SDE_ORA_GLBalanceFact_Full] Run Instance Name: [] Run Id: [2324]
DIRECTOR> TM_6101 Mapping name: SDE_ORA_GLBalanceFact [version 1].
DIRECTOR> TM_6963 Pre 85 Timestamp Compatibility is Enabled
DIRECTOR> TM_6964 Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
DIRECTOR> TM_6827 [E:\Informatica\9.0.1\server\infa_shared\Storage] will be used as storage directory for session [SDE_ORA_GLBalanceFact_Full].
DIRECTOR> CMN_1805 Recovery cache will be deleted when running in normal mode.
DIRECTOR> CMN_1802 Session recovery cache initialization is complete.
DIRECTOR> TM_6708 Using configuration property [DisableDB2BulkMode,Yes]
DIRECTOR> TM_6708 Using configuration property [ServerPort,4006]
DIRECTOR> TM_6708 Using configuration property [SiebelUnicodeDB,apps@R12PLY baw@orcl]
DIRECTOR> TM_6708 Using configuration property [overrideMptlVarWithMapVar,Yes]
DIRECTOR> TM_6703 Session [SDE_ORA_GLBalanceFact_Full] is run by 64-bit Integration Service [node01_OBIEETESTAPP], version [9.0.1 HotFix2], build [1111].
MANAGER> PETL_24058 Running Partition Group [1].
MANAGER> PETL_24000 Parallel Pipeline Engine initializing.
MANAGER> PETL_24001 Parallel Pipeline Engine running.
MANAGER> PETL_24003 Initializing session run.
MAPPING> CMN_1569 Server Mode: [ASCII]
MAPPING> CMN_1570 Server Code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
MAPPING> TM_6151 The session sort order is [Binary].
MAPPING> TM_6156 Using low precision processing.
MAPPING> TM_6180 Deadlock retry logic will not be implemented.
MAPPING> TM_6187 Session target-based commit interval is [10000].
MAPPING> TM_6307 DTM error log disabled.
MAPPING> TE_7022 TShmWriter: Initialized
MAPPING> TM_6007 DTM initialized successfully for session [SDE_ORA_GLBalanceFact_Full]
DIRECTOR> PETL_24033 All DTM Connection Info: [<NONE>].
MANAGER> PETL_24004 Starting pre-session tasks. : (Fri Apr 12 22:05:37 2013)
MANAGER> PETL_24027 Pre-session task completed successfully. : (Fri Apr 12 22:05:37 2013)
DIRECTOR> PETL_24006 Starting data movement.
MAPPING> TM_6660 Total Buffer Pool size is 12582912 bytes and Block size is 128000 bytes.
READER_1_1_1> DBG_21438 Reader: Source is [R12PLY], user [apps]
READER_1_1_1> BLKR_16003 Initialization completed successfully.
WRITER_1_*_1> WRT_8146 Writer: Target is database [orcl], user [baw], bulk mode [ON]
WRITER_1_*_1> WRT_8106 Warning! Bulk Mode session - recovery is not guaranteed.
WRITER_1_*_1> WRT_8124 Target Table W_ACCT_BUDGET_FS :SQL INSERT statement:
INSERT INTO W_ACCT_BUDGET_FS(GL_ACCOUNT_ID,LEDGER_ID,BUDGET_LEDGER_ID,BUDGET_ID,PERIOD_BEGIN_DT,PERIOD_END_DT,BUDGET_LOC_AMT,LOC_CURR_CODE,CHANGED_BY_ID,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,ADJUSTMENT_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,TENANT_ID,X_CUSTOM) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
WRITER_1_*_1> WRT_8146 Writer: Target is database [orcl], user [baw], bulk mode [ON]
WRITER_1_*_1> WRT_8106 Warning! Bulk Mode session - recovery is not guaranteed.
WRITER_1_*_1> WRT_8124 Target Table W_GL_BALANCE_FS :SQL INSERT statement:
INSERT INTO W_GL_BALANCE_FS(BUSN_AREA_ORG_ID,GL_ACCOUNT_ID,BALANCE_DATE,DB_CR_IND,BALANCE_ACCT_AMT,BALANCE_LOC_AMT,ACTIVITY_ACCT_AMT,ACTIVITY_LOC_AMT,ACCT_CURR_CODE,LOC_CURR_CODE,INTEGRATION_ID,DATASOURCE_NUM_ID,TENANT_ID,X_CUSTOM,ADJUSTMENT_FLAG,BALANCE_TYPE_FLAG,TRANSLATED_FLAG,SUMMARY_ACCOUNT_FLAG,LAST_UPDATE_DATE,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,LEDGER_ID) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
WRITER_1_*_1> WRT_8270 Target connection group #1 consists of target(s) [W_ACCT_BUDGET_FS, W_GL_BALANCE_FS]
WRITER_1_*_1> WRT_8003 Writer initialization complete.
READER_1_1_1> BLKR_16007 Reader run started.
WRITER_1_*_1> WRT_8005 Writer run started.
WRITER_1_*_1> WRT_8158
*****START LOAD SESSION*****
Load Start Time: Fri Apr 12 22:05:37 2013
Target tables:
W_ACCT_BUDGET_FS
W_GL_BALANCE_FS
READER_1_1_1> RR_4029 SQ Instance [mplt_BC_ORA_GL_Balance_Fact.SQ_GL_BALANCES] User specified SQL Query [SELECT
BAL.LEDGER_ID,
BAL.CODE_COMBINATION_ID,
BAL.CURRENCY_CODE,
LED.CURRENCY_CODE,
PER.PERIOD_NAME,
BAL.ACTUAL_FLAG,
BAL.TRANSLATED_FLAG,
BAL.TEMPLATE_ID,
BAL.PERIOD_NET_DR,
BAL.PERIOD_NET_CR,
( BAL.BEGIN_BALANCE_DR + BAL.PERIOD_NET_DR ),
( BAL.BEGIN_BALANCE_CR + BAL.PERIOD_NET_CR) ,
BAL.PERIOD_NET_DR_BEQ,
BAL.PERIOD_NET_CR_BEQ,
( BAL.BEGIN_BALANCE_DR_BEQ + BAL.PERIOD_NET_DR_BEQ ) PERIOD_END_BALANCE_DR_BEQ,
( BAL.BEGIN_BALANCE_CR_BEQ + BAL.PERIOD_NET_CR_BEQ ) PERIOD_END_BALANCE_CR_BEQ,
PER.START_DATE,
PER.END_DATE,
BAL.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_BAL,
BAL.LAST_UPDATED_BY AS LAST_UPDATED_BY_BAL,
PER.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_PERIODS,
PER.LAST_UPDATED_BY AS LAST_UPDATED_BY_PERIODS,
LED.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_SOB,
LED.LAST_UPDATED_BY AS LAST_UPDATED_BY_SOB,
BAL.BUDGET_VERSION_ID AS BUDGET_VERSION_ID,
PER.ADJUSTMENT_PERIOD_FLAG AS ADJUSTMENT_PERIOD_FLAG,
CASE WHEN
BAL.TRANSLATED_FLAG = 'Y' THEN 'TRANSLATED'
WHEN
BAL.TRANSLATED_FLAG = 'R' THEN 'ENTERED_FOREIGN'
WHEN
BAL.CURRENCY_CODE = 'STAT' THEN 'STAT'
WHEN
((BAL.PERIOD_NET_DR_BEQ = 0) OR (BAL.PERIOD_NET_DR_BEQ IS NULL)) AND
((BAL.PERIOD_NET_CR_BEQ = 0) OR (BAL.PERIOD_NET_CR_BEQ IS NULL)) AND
((BAL.BEGIN_BALANCE_DR_BEQ = 0) OR (BAL.BEGIN_BALANCE_DR_BEQ IS NULL)) AND
((BAL.BEGIN_BALANCE_CR_BEQ = 0) OR (BAL.BEGIN_BALANCE_CR_BEQ IS NULL))
THEN 'BASE'
ELSE 'ENTERED_LEDGER'
END CURRENCY_BALANCE_TYPE
FROM
GL_BALANCES BAL
, GL_LEDGERS LED
, GL_PERIODS PER
WHERE LED.LEDGER_ID = BAL.LEDGER_ID
AND PER.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND BAL.PERIOD_NAME = PER.PERIOD_NAME
AND BAL.PERIOD_TYPE = PER.PERIOD_TYPE
AND NVL(BAL.TRANSLATED_FLAG, 'X') IN ('Y', 'X', 'R')
AND BAL.ACTUAL_FLAG IN ( 'A','B')
AND BAL.TEMPLATE_ID IS NULL
AND
(
BAL.LAST_UPDATE_DATE >=
TO_DATE('', 'MM/DD/YYYY HH24:MI:SS')
OR PER.LAST_UPDATE_DATE >=
TO_DATE('', 'MM/DD/YYYY HH24:MI:SS')
)
AND DECODE(, 'Y', LED.LEDGER_ID, 1) IN ()
AND DECODE(, 'Y', LED.LEDGER_CATEGORY_CODE, 'NONE') IN ()]
READER_1_1_1> RR_4049 SQL Query issued to database : (Fri Apr 12 22:05:37 2013)
READER_1_1_1> CMN_1761 Timestamp Event: [Fri Apr 12 22:05:37 2013]
*READER_1_1_1> RR_4035 SQL Error [*
ORA-00936: missing expression
Database driver error...
Function Name : Execute
SQL Stmt : SELECT
BAL.LEDGER_ID,
BAL.CODE_COMBINATION_ID,
BAL.CURRENCY_CODE,
LED.CURRENCY_CODE,
PER.PERIOD_NAME,
BAL.ACTUAL_FLAG,
BAL.TRANSLATED_FLAG,
BAL.TEMPLATE_ID,
BAL.PERIOD_NET_DR,
BAL.PERIOD_NET_CR,
( BAL.BEGIN_BALANCE_DR + BAL.PERIOD_NET_DR ),
( BAL.BEGIN_BALANCE_CR + BAL.PERIOD_NET_CR) ,
BAL.PERIOD_NET_DR_BEQ,
BAL.PERIOD_NET_CR_BEQ,
( BAL.BEGIN_BALANCE_DR_BEQ + BAL.PERIOD_NET_DR_BEQ ) PERIOD_END_BALANCE_DR_BEQ,
( BAL.BEGIN_BALANCE_CR_BEQ + BAL.PERIOD_NET_CR_BEQ ) PERIOD_END_BALANCE_CR_BEQ,
PER.START_DATE,
PER.END_DATE,
BAL.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_BAL,
BAL.LAST_UPDATED_BY AS LAST_UPDATED_BY_BAL,
PER.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_PERIODS,
PER.LAST_UPDATED_BY AS LAST_UPDATED_BY_PERIODS,
LED.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_SOB,
LED.LAST_UPDATED_BY AS LAST_UPDATED_BY_SOB,
BAL.BUDGET_VERSION_ID AS BUDGET_VERSION_ID,
PER.ADJUSTMENT_PERIOD_FLAG AS ADJUSTMENT_PERIOD_FLAG,
CASE WHEN
BAL.TRANSLATED_FLAG = 'Y' THEN 'TRANSLATED'
WHEN
BAL.TRANSLATED_FLAG = 'R' THEN 'ENTERED_FOREIGN'
WHEN
BAL.CURRENCY_CODE = 'STAT' THEN 'STAT'
WHEN
((BAL.PERIOD_NET_DR_BEQ = 0) OR (BAL.PERIOD_NET_DR_BEQ IS NULL)) AND
((BAL.PERIOD_NET_CR_BEQ = 0) OR (BAL.PERIOD_NET_CR_BEQ IS NULL)) AND
((BAL.BEGIN_BALANCE_DR_BEQ = 0) OR (BAL.BEGIN_BALANCE_DR_BEQ IS NULL)) AND
((BAL.BEGIN_BALANCE_CR_BEQ = 0) OR (BAL.BEGIN_BALANCE_CR_BEQ IS NULL))
THEN 'BASE'
ELSE 'ENTERED_LEDGER'
END CURRENCY_BALANCE_TYPE
FROM
GL_BALANCES BAL
, GL_LEDGERS LED
, GL_PERIODS PER
WHERE LED.LEDGER_ID = BAL.LEDGER_ID
AND PER.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND BAL.PERIOD_NAME = PER.PERIOD_NAME
AND BAL.PERIOD_TYPE = PER.PERIOD_TYPE
AND NVL(BAL.TRANSLATED_FLAG, 'X') IN ('Y', 'X', 'R')
AND BAL.ACTUAL_FLAG IN ( 'A','B')
AND BAL.TEMPLATE_ID IS NULL
AND
(
BAL.LAST_UPDATE_DATE >=
TO_DATE('', 'MM/DD/YYYY HH24:MI:SS')
OR PER.LAST_UPDATE_DATE >=
TO_DATE('', 'MM/DD/YYYY HH24:MI:SS')
)
AND DECODE(, 'Y', LED.LEDGER_ID, 1) IN ()
AND DECODE(, 'Y', LED.LEDGER_CATEGORY_CODE, 'NONE') IN ()
Oracle Fatal Error
Database driver error...
Function Name : Execute
SQL Stmt : SELECT
BAL.LEDGER_ID,
BAL.CODE_COMBINATION_ID,
BAL.CURRENCY_CODE,
LED.CURRENCY_CODE,
PER.PERIOD_NAME,
BAL.ACTUAL_FLAG,
BAL.TRANSLATED_FLAG,
BAL.TEMPLATE_ID,
BAL.PERIOD_NET_DR,
BAL.PERIOD_NET_CR,
( BAL.BEGIN_BALANCE_DR + BAL.PERIOD_NET_DR ),
( BAL.BEGIN_BALANCE_CR + BAL.PERIOD_NET_CR) ,
BAL.PERIOD_NET_DR_BEQ,
BAL.PERIOD_NET_CR_BEQ,
( BAL.BEGIN_BALANCE_DR_BEQ + BAL.PERIOD_NET_DR_BEQ ) PERIOD_END_BALANCE_DR_BEQ,
( BAL.BEGIN_BALANCE_CR_BEQ + BAL.PERIOD_NET_CR_BEQ ) PERIOD_END_BALANCE_CR_BEQ,
PER.START_DATE,
PER.END_DATE,
BAL.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_BAL,
BAL.LAST_UPDATED_BY AS LAST_UPDATED_BY_BAL,
PER.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_PERIODS,
PER.LAST_UPDATED_BY AS LAST_UPDATED_BY_PERIODS,
LED.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_SOB,
LED.LAST_UPDATED_BY AS LAST_UPDATED_BY_SOB,
BAL.BUDGET_VERSION_ID AS BUDGET_VERSION_ID,
PER.ADJUSTMENT_PERIOD_FLAG AS ADJUSTMENT_PERIOD_FLAG,
CASE WHEN
BAL.TRANSLATED_FLAG = 'Y' THEN 'TRANSLATED'
WHEN
BAL.TRANSLATED_FLAG = 'R' THEN 'ENTERED_FOREIGN'
WHEN
BAL.CURRENCY_CODE = 'STAT' THEN 'STAT'
WHEN
((BAL.PERIOD_NET_DR_BEQ = 0) OR (BAL.PERIOD_NET_DR_BEQ IS NULL)) AND
((BAL.PERIOD_NET_CR_BEQ = 0) OR (BAL.PERIOD_NET_CR_BEQ IS NULL)) AND
((BAL.BEGIN_BALANCE_DR_BEQ = 0) OR (BAL.BEGIN_BALANCE_DR_BEQ IS NULL)) AND
((BAL.BEGIN_BALANCE_CR_BEQ = 0) OR (BAL.BEGIN_BALANCE_CR_BEQ IS NULL))
THEN 'BASE'
ELSE 'ENTERED_LEDGER'
END CURRENCY_BALANCE_TYPE
FROM
GL_BALANCES BAL
, GL_LEDGERS LED
, GL_PERIODS PER
WHERE LED.LEDGER_ID = BAL.LEDGER_ID
AND PER.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND BAL.PERIOD_NAME = PER.PERIOD_NAME
AND BAL.PERIOD_TYPE = PER.PERIOD_TYPE
AND NVL(BAL.TRANSLATED_FLAG, 'X') IN ('Y', 'X', 'R')
AND BAL.ACTUAL_FLAG IN ( 'A','B')
AND BAL.TEMPLATE_ID IS NULL
AND
(
BAL.LAST_UPDATE_DATE >=
TO_DATE('', 'MM/DD/YYYY HH24:MI:SS')
OR PER.LAST_UPDATE_DATE >=
TO_DATE('', 'MM/DD/YYYY HH24:MI:SS')
)
AND DECODE(, 'Y', LED.LEDGER_ID, 1) IN ()
AND DECODE(, 'Y', LED.LEDGER_CATEGORY_CODE, 'NONE') IN ()
Oracle Fatal Error].
READER_1_1_1> CMN_1761 Timestamp Event: [Fri Apr 12 22:05:37 2013]
READER_1_1_1> BLKR_16004 ERROR: Prepare failed.
WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.
WRITER_1_*_1> WRT_8325 Final rollback executed for the target [W_ACCT_BUDGET_FS, W_GL_BALANCE_FS] at end of load
WRITER_1_*_1> WRT_8035 Load complete time: Fri Apr 12 22:05:37 2013
LOAD SUMMARY
============
WRT_8036 Target: W_ACCT_BUDGET_FS (Instance Name: [W_ACCT_BUDGET_FS])
WRT_8044 No data loaded for this target
WRT_8036 Target: W_GL_BALANCE_FS (Instance Name: [W_GL_BALANCE_FS])
WRT_8044 No data loaded for this target
WRITER_1__1> WRT_8043 ****END LOAD SESSION*****
MANAGER> PETL_24031
***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****
Thread [READER_1_1_1] created for [the read stage] of partition point [mplt_BC_ORA_GL_Balance_Fact.SQ_GL_BALANCES] has completed. The total run time was insufficient for any meaningful statistics.
Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [mplt_BC_ORA_GL_Balance_Fact.SQ_GL_BALANCES] has completed. The total run time was insufficient for any meaningful statistics.
Thread [WRITER_1_*_1] created for [the write stage] of partition point [W_ACCT_BUDGET_FS, W_GL_BALANCE_FS] has completed. The total run time was insufficient for any meaningful statistics.
MANAGER> PETL_24005 Starting post-session tasks. : (Fri Apr 12 22:05:37 2013)
MANAGER> PETL_24029 Post-session task completed successfully. : (Fri Apr 12 22:05:38 2013)
MAPPING> TM_6018 The session completed with [0] row transformation errors.
MANAGER> PETL_24002 Parallel Pipeline Engine finished.
DIRECTOR> PETL_24013 Session run completed with failure.
DIRECTOR> TM_6022
SESSION LOAD SUMMARY
================================================
DIRECTOR> TM_6252 Source Load Summary.
DIRECTOR> CMN_1740 Table: [SQ_GL_BALANCES] (Instance Name: [mplt_BC_ORA_GL_Balance_Fact.SQ_GL_BALANCES])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
DIRECTOR> TM_6253 Target Load Summary.
DIRECTOR> CMN_1740 Table: [W_ACCT_BUDGET_FS] (Instance Name: [W_ACCT_BUDGET_FS])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
DIRECTOR> CMN_1740 Table: [W_GL_BALANCE_FS] (Instance Name: [W_GL_BALANCE_FS])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
DIRECTOR> TM_6023
===================================================
DIRECTOR> TM_6020 Session [SDE_ORA_GLBalanceFact_Full] completed at [Fri Apr 12 22:05:38 2013].