ETL failed with ORA-01722
592748May 14 2009 — edited Feb 22 2010SDE_ORA_InventoryProductDimension failed, error code is 3306.
Gernally the issus seems because the "ORA-01722: invalid number" failed when INSERT INTO W_INVENTORY_PRODUCT_DS
How to debug this. Thanks in advance.
The detail log of the workflow is:
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6228 : Writing session output to log file [e:\Informatica\PowerCenter8.1.1\server\infa_shared\SessLogs\SDE_ORAR12_Adaptor.SDE_ORA_InventoryProductDimension_Derive.log].
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : VAR_27028 : Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : VAR_27028 : Use override value [9] for mapping parameter:[$$DATASOURCE_NUM_ID].
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : VAR_27028 : Use override value [27] for mapping parameter:[$$INVPROD_CAT_SET_ID].
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : VAR_27028 : Use override value [DEFAULT] for mapping parameter:[$$TENANT_ID].
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6014 : Initializing session [SDE_ORA_InventoryProductDimension_Derive] at [Wed May 13 22:50:01 2009]
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6683 : Repository Name: [Oracle_BI_DW_Base]
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6684 : Server Name: [Oracle_BI_DW_Base_Integration_Service]
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6686 : Folder: [SDE_ORAR12_Adaptor]
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6685 : Workflow: [SDE_ORA_InventoryProductDimension_Full]
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6101 : Mapping name: SDE_ORA_InventoryProductDimension_Derive [version 1]
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6827 : [e:\Informatica\PowerCenter8.1.1\server\infa_shared\Storage] will be used as storage directory for session [SDE_ORA_InventoryProductDimension_Derive].
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : CMN_1805 : Recovery cache will be deleted when running in normal mode.
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : CMN_1802 : Session recovery cache initialization is complete.
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6703 : Session [SDE_ORA_InventoryProductDimension_Derive] is run by 32-bit Integration Service [node01_xpone], version [8.1.1 SP4], build [0817].
2009-05-13 22:50:01 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24058 : Running Partition Group [1].
2009-05-13 22:50:01 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24000 : Parallel Pipeline Engine initializing.
2009-05-13 22:50:01 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24001 : Parallel Pipeline Engine running.
2009-05-13 22:50:01 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24003 : Initializing session run.
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : CMN_1569 : Server Mode: [ASCII]
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : CMN_1570 : Server Code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6151 : Session Sort Order: [Binary]
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6155 : Using HIGH precision decimal arithmetic
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6180 : Deadlock retry logic will not be implemented.
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6307 : DTM Error Log Disabled.
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TE_7022 : TShmWriter: Initialized
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6007 : DTM initialized successfully for session [SDE_ORA_InventoryProductDimension_Derive]
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24033 : All DTM Connection Info: [<NONE>].
2009-05-13 22:50:01 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24004 : PETL_24004 Starting pre-session tasks. : (Wed May 13 22:50:01 2009)
2009-05-13 22:50:01 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24027 : PETL_24027 Pre-session task completed successfully. : (Wed May 13 22:50:01 2009)
2009-05-13 22:50:01 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24006 : Starting data movement.
2009-05-13 22:50:01 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6660 : Total Buffer Pool size is 32000000 bytes and Block size is 128000 bytes.
2009-05-13 22:50:01 : INFO : (620 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : DBG_21438 : Reader: Source is [orcl], user [olap]
2009-05-13 22:50:01 : INFO : (620 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : BLKR_16003 : Initialization completed successfully.
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8146 : Writer: Target is database [orcl], user [olap], bulk mode [ON]
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8106 : Warning! Bulk Mode session - recovery is not guaranteed.
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8124 : Target Table W_INVENTORY_PRODUCT_DS :SQL INSERT statement:
INSERT INTO W_INVENTORY_PRODUCT_DS(PRODUCT_ID,INVENTORY_ORG_ID,PLANT_LOC_ID,PRODUCT_NUM,PRODUCT_DESC,ABC_IND,PLANNER_CODE,PLANNER_NAME,PROCUREMENT_TYPE_CODE,PROCUREMENT_TYPE_NAME,SPC_PROC_TYPE_CODE,SPC_PROC_TYPE_NAME,BUYER_CODE,BUYER_NAME,COMMODITY_CODE,COMMODITY_NAME,COMMODITY_UOM_CODE,COMMODITY_UOM_NAME,PROFIT_CENTER_NUM,PROFIT_CENTER_NAME,REORDER_POINT,SAFETY_STOCK_LEVEL,MIN_LOT_SIZE,MAX_LOT_SIZE,FIXED_LOT_SIZE,MAX_STOCK_LEVEL,LOT_ORDERING_COST,MRP_TIME_FENCE,EXT_PROCURE_TIME,INTERNAL_MFG_TIME,MAX_STORAGE_DAYS,MRP_PROFILE_CODE,MRP_PROFILE_NAME,MRP_TYPE_CODE,MRP_TYPE_NAME,MRP_GRP_CODE,MRP_GRP_NAME,LOT_SIZE_CODE,LOT_SIZE_NAME,BACKFLUSH_IND,QA_INSPECT_IND,REPETITIVE_MFG_IND,BULK_ITEM_IND,FORECAST_PERIOD,MFG_UOM_CODE,MFG_UOM_NAME,ISSUE_UOM_CODE,ISSUE_UOM_NAME,MANUFACTURING_PLACE,LOADING_TYPE_CODE,LOADING_TYPE_NAME,INT_STORE_LOC_CODE,INT_STORE_LOC_NAME,EXT_STORE_LOC_CODE,EXT_STORE_LOC_NAME,ACTIVE_FLG,CREATED_BY_ID,CHANGED_BY_ID,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,SRC_EFF_FROM_DT,SRC_EFF_TO_DT,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,TENANT_ID,X_CUSTOM,PRODUCT_HIER1_CODE,PRODUCT_HIER1_NAME,PRODUCT_HIER2_CODE,PRODUCT_HIER2_NAME,PRODUCT_HIER3_CODE,PRODUCT_HIER3_NAME,PRODUCT_HIER4_CODE,PRODUCT_HIER4_NAME,PRODUCT_HIER5_CODE,PRODUCT_HIER5_NAME,PRODUCT_HIER6_CODE,PRODUCT_HIER6_NAME) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8124 : Target Table W_INVENTORY_PRODUCT_DS :SQL UPDATE statement:
UPDATE W_INVENTORY_PRODUCT_DS SET PRODUCT_ID = ?, INVENTORY_ORG_ID = ?, PLANT_LOC_ID = ?, PRODUCT_NUM = ?, PRODUCT_DESC = ?, ABC_IND = ?, PLANNER_CODE = ?, PLANNER_NAME = ?, PROCUREMENT_TYPE_CODE = ?, PROCUREMENT_TYPE_NAME = ?, SPC_PROC_TYPE_CODE = ?, SPC_PROC_TYPE_NAME = ?, BUYER_CODE = ?, BUYER_NAME = ?, COMMODITY_CODE = ?, COMMODITY_NAME = ?, COMMODITY_UOM_CODE = ?, COMMODITY_UOM_NAME = ?, PROFIT_CENTER_NUM = ?, PROFIT_CENTER_NAME = ?, REORDER_POINT = ?, SAFETY_STOCK_LEVEL = ?, MIN_LOT_SIZE = ?, MAX_LOT_SIZE = ?, FIXED_LOT_SIZE = ?, MAX_STOCK_LEVEL = ?, LOT_ORDERING_COST = ?, MRP_TIME_FENCE = ?, EXT_PROCURE_TIME = ?, INTERNAL_MFG_TIME = ?, MAX_STORAGE_DAYS = ?, MRP_PROFILE_CODE = ?, MRP_PROFILE_NAME = ?, MRP_TYPE_CODE = ?, MRP_TYPE_NAME = ?, MRP_GRP_CODE = ?, MRP_GRP_NAME = ?, LOT_SIZE_CODE = ?, LOT_SIZE_NAME = ?, BACKFLUSH_IND = ?, QA_INSPECT_IND = ?, REPETITIVE_MFG_IND = ?, BULK_ITEM_IND = ?, FORECAST_PERIOD = ?, MFG_UOM_CODE = ?, MFG_UOM_NAME = ?, ISSUE_UOM_CODE = ?, ISSUE_UOM_NAME = ?, MANUFACTURING_PLACE = ?, LOADING_TYPE_CODE = ?, LOADING_TYPE_NAME = ?, INT_STORE_LOC_CODE = ?, INT_STORE_LOC_NAME = ?, EXT_STORE_LOC_CODE = ?, EXT_STORE_LOC_NAME = ?, ACTIVE_FLG = ?, CREATED_BY_ID = ?, CHANGED_BY_ID = ?, CREATED_ON_DT = ?, CHANGED_ON_DT = ?, AUX1_CHANGED_ON_DT = ?, AUX2_CHANGED_ON_DT = ?, AUX3_CHANGED_ON_DT = ?, AUX4_CHANGED_ON_DT = ?, SRC_EFF_TO_DT = ?, DELETE_FLG = ?, TENANT_ID = ?, X_CUSTOM = ?, PRODUCT_HIER1_CODE = ?, PRODUCT_HIER1_NAME = ?, PRODUCT_HIER2_CODE = ?, PRODUCT_HIER2_NAME = ?, PRODUCT_HIER3_CODE = ?, PRODUCT_HIER3_NAME = ?, PRODUCT_HIER4_CODE = ?, PRODUCT_HIER4_NAME = ?, PRODUCT_HIER5_CODE = ?, PRODUCT_HIER5_NAME = ?, PRODUCT_HIER6_CODE = ?, PRODUCT_HIER6_NAME = ? WHERE SRC_EFF_FROM_DT = ? AND DATASOURCE_NUM_ID = ? AND INTEGRATION_ID = ?
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8124 : Target Table W_INVENTORY_PRODUCT_DS :SQL DELETE statement:
DELETE FROM W_INVENTORY_PRODUCT_DS WHERE SRC_EFF_FROM_DT = ? AND DATASOURCE_NUM_ID = ? AND INTEGRATION_ID = ?
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8270 : Target connection group #1 consists of target(s) [W_INVENTORY_PRODUCT_DS]
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8003 : Writer initialization complete.
2009-05-13 22:50:01 : INFO : (620 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : BLKR_16007 : Reader run started.
2009-05-13 22:50:01 : INFO : (620 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : RR_4010 : SQ instance [SQ_W_ORA_INVPROD_DS_TMP] SQL Query [SELECT W_ORA_INVPROD_DS_TMP.PRODUCT_ID, W_ORA_INVPROD_DS_TMP.INVENTORY_ORG_ID, W_ORA_INVPROD_DS_TMP.PLANT_LOC_ID, W_ORA_INVPROD_DS_TMP.PRODUCT_NUM, W_ORA_INVPROD_DS_TMP.PRODUCT_DESC, W_ORA_INVPROD_DS_TMP.ABC_IND, W_ORA_INVPROD_DS_TMP.PLANNER_CODE, W_ORA_INVPROD_DS_TMP.PLANNER_NAME, W_ORA_INVPROD_DS_TMP.PROCUREMENT_TYPE_CODE, W_ORA_INVPROD_DS_TMP.PROCUREMENT_TYPE_NAME, W_ORA_INVPROD_DS_TMP.SPC_PROC_TYPE_CODE, W_ORA_INVPROD_DS_TMP.SPC_PROC_TYPE_NAME, W_ORA_INVPROD_DS_TMP.BUYER_CODE, W_ORA_INVPROD_DS_TMP.BUYER_NAME, W_ORA_INVPROD_DS_TMP.COMMODITY_CODE, W_ORA_INVPROD_DS_TMP.COMMODITY_NAME, W_ORA_INVPROD_DS_TMP.COMMODITY_UOM_CODE, W_ORA_INVPROD_DS_TMP.COMMODITY_UOM_NAME, W_ORA_INVPROD_DS_TMP.PROFIT_CENTER_NUM, W_ORA_INVPROD_DS_TMP.PROFIT_CENTER_NAME, W_ORA_INVPROD_DS_TMP.REORDER_POINT, W_ORA_INVPROD_DS_TMP.SAFETY_STOCK_LEVEL, W_ORA_INVPROD_DS_TMP.MIN_LOT_SIZE, W_ORA_INVPROD_DS_TMP.MAX_LOT_SIZE, W_ORA_INVPROD_DS_TMP.FIXED_LOT_SIZE, W_ORA_INVPROD_DS_TMP.MAX_STOCK_LEVEL, W_ORA_INVPROD_DS_TMP.LOT_ORDERING_COST, W_ORA_INVPROD_DS_TMP.MRP_TIME_FENCE, W_ORA_INVPROD_DS_TMP.EXT_PROCURE_TIME, W_ORA_INVPROD_DS_TMP.INTERNAL_MFG_TIME, W_ORA_INVPROD_DS_TMP.MAX_STORAGE_DAYS, W_ORA_INVPROD_DS_TMP.MRP_PROFILE_CODE, W_ORA_INVPROD_DS_TMP.MRP_PROFILE_NAME, W_ORA_INVPROD_DS_TMP.MRP_TYPE_CODE, W_ORA_INVPROD_DS_TMP.MRP_TYPE_NAME, W_ORA_INVPROD_DS_TMP.MRP_GRP_CODE, W_ORA_INVPROD_DS_TMP.MRP_GRP_NAME, W_ORA_INVPROD_DS_TMP.LOT_SIZE_CODE, W_ORA_INVPROD_DS_TMP.LOT_SIZE_NAME, W_ORA_INVPROD_DS_TMP.BACKFLUSH_IND, W_ORA_INVPROD_DS_TMP.QA_INSPECT_IND, W_ORA_INVPROD_DS_TMP.REPETITIVE_MFG_IND, W_ORA_INVPROD_DS_TMP.BULK_ITEM_IND, W_ORA_INVPROD_DS_TMP.FORECAST_PERIOD, W_ORA_INVPROD_DS_TMP.MFG_UOM_CODE, W_ORA_INVPROD_DS_TMP.MFG_UOM_NAME, W_ORA_INVPROD_DS_TMP.ISSUE_UOM_CODE, W_ORA_INVPROD_DS_TMP.ISSUE_UOM_NAME, W_ORA_INVPROD_DS_TMP.MANUFACTURING_PLACE, W_ORA_INVPROD_DS_TMP.LOADING_TYPE_CODE, W_ORA_INVPROD_DS_TMP.LOADING_TYPE_NAME, W_ORA_INVPROD_DS_TMP.INT_STORE_LOC_CODE, W_ORA_INVPROD_DS_TMP.INT_STORE_LOC_NAME, W_ORA_INVPROD_DS_TMP.EXT_STORE_LOC_CODE, W_ORA_INVPROD_DS_TMP.EXT_STORE_LOC_NAME, W_ORA_INVPROD_DS_TMP.ACTIVE_FLG, W_ORA_INVPROD_DS_TMP.CREATED_BY_ID, W_ORA_INVPROD_DS_TMP.CHANGED_BY_ID, W_ORA_INVPROD_DS_TMP.CREATED_ON_DT, W_ORA_INVPROD_DS_TMP.CHANGED_ON_DT, W_ORA_INVPROD_DS_TMP.AUX1_CHANGED_ON_DT, W_ORA_INVPROD_DS_TMP.AUX2_CHANGED_ON_DT, W_ORA_INVPROD_DS_TMP.AUX3_CHANGED_ON_DT, W_ORA_INVPROD_DS_TMP.AUX4_CHANGED_ON_DT, W_ORA_INVPROD_DS_TMP.SRC_EFF_FROM_DT, W_ORA_INVPROD_DS_TMP.SRC_EFF_TO_DT, W_ORA_INVPROD_DS_TMP.DELETE_FLG, W_ORA_INVPROD_DS_TMP.DATASOURCE_NUM_ID, W_ORA_INVPROD_DS_TMP.INTEGRATION_ID, W_ORA_INVPROD_DS_TMP.TENANT_ID, W_ORA_INVPROD_DS_TMP.X_CUSTOM, W_ORA_PRODUCT_CTGY_TMP.PROD_HIER1_CODE, W_ORA_PRODUCT_CTGY_TMP.PROD_HIER2_CODE, W_ORA_PRODUCT_CTGY_TMP.PROD_HIER3_CODE, W_ORA_PRODUCT_CTGY_TMP.PROD_HIER4_CODE, W_ORA_PRODUCT_CTGY_TMP.PROD_HIER5_CODE, W_ORA_PRODUCT_CTGY_TMP.PROD_HIER6_CODE FROM W_ORA_PRODUCT_CTGY_TMP, W_ORA_INVPROD_DS_TMP WHERE {W_ORA_INVPROD_DS_TMP
LEFT OUTER JOIN W_ORA_PRODUCT_CTGY_TMP ON
W_ORA_INVPROD_DS_TMP.PRODUCT_ID =W_ORA_PRODUCT_CTGY_TMP.INVENTORY_ITEM_ID AND
W_ORA_INVPROD_DS_TMP.INVENTORY_ORG_ID = W_ORA_PRODUCT_CTGY_TMP.ORGANIZATION_ID AND
W_ORA_PRODUCT_CTGY_TMP.CATEGORY_SET_ID = 27
}]
2009-05-13 22:50:01 : INFO : (620 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : RR_4049 : RR_4049 SQL Query issued to database : (Wed May 13 22:50:01 2009)
2009-05-13 22:50:01 : INFO : (620 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : RR_4011 : Informatica inner/outer join syntax found, SQL query converted to [SELECT
W_ORA_INVPROD_DS_TMP.PRODUCT_ID , W_ORA_INVPROD_DS_TMP.INVENTORY_ORG_ID , W_ORA_INVPROD_DS_TMP.PLANT_LOC_ID , W_ORA_INVPROD_DS_TMP.PRODUCT_NUM , W_ORA_INVPROD_DS_TMP.PRODUCT_DESC , W_ORA_INVPROD_DS_TMP.ABC_IND , W_ORA_INVPROD_DS_TMP.PLANNER_CODE , W_ORA_INVPROD_DS_TMP.PLANNER_NAME , W_ORA_INVPROD_DS_TMP.PROCUREMENT_TYPE_CODE , W_ORA_INVPROD_DS_TMP.PROCUREMENT_TYPE_NAME , W_ORA_INVPROD_DS_TMP.SPC_PROC_TYPE_CODE , W_ORA_INVPROD_DS_TMP.SPC_PROC_TYPE_NAME , W_ORA_INVPROD_DS_TMP.BUYER_CODE , W_ORA_INVPROD_DS_TMP.BUYER_NAME , W_ORA_INVPROD_DS_TMP.COMMODITY_CODE , W_ORA_INVPROD_DS_TMP.COMMODITY_NAME , W_ORA_INVPROD_DS_TMP.COMMODITY_UOM_CODE , W_ORA_INVPROD_DS_TMP.COMMODITY_UOM_NAME , W_ORA_INVPROD_DS_TMP.PROFIT_CENTER_NUM , W_ORA_INVPROD_DS_TMP.PROFIT_CENTER_NAME , W_ORA_INVPROD_DS_TMP.REORDER_POINT , W_ORA_INVPROD_DS_TMP.SAFETY_STOCK_LEVEL , W_ORA_INVPROD_DS_TMP.MIN_LOT_SIZE , W_ORA_INVPROD_DS_TMP.MAX_LOT_SIZE , W_ORA_INVPROD_DS_TMP.FIXED_LOT_SIZE , W_ORA_INVPROD_DS_TMP.MAX_STOCK_LEVEL , W_ORA_INVPROD_DS_TMP.LOT_ORDERING_COST , W_ORA_INVPROD_DS_TMP.MRP_TIME_FENCE , W_ORA_INVPROD_DS_TMP.EXT_PROCURE_TIME , W_ORA_INVPROD_DS_TMP.INTERNAL_MFG_TIME , W_ORA_INVPROD_DS_TMP.MAX_STORAGE_DAYS , W_ORA_INVPROD_DS_TMP.MRP_PROFILE_CODE , W_ORA_INVPROD_DS_TMP.MRP_PROFILE_NAME , W_ORA_INVPROD_DS_TMP.MRP_TYPE_CODE , W_ORA_INVPROD_DS_TMP.MRP_TYPE_NAME , W_ORA_INVPROD_DS_TMP.MRP_GRP_CODE , W_ORA_INVPROD_DS_TMP.MRP_GRP_NAME , W_ORA_INVPROD_DS_TMP.LOT_SIZE_CODE , W_ORA_INVPROD_DS_TMP.LOT_SIZE_NAME , W_ORA_INVPROD_DS_TMP.BACKFLUSH_IND , W_ORA_INVPROD_DS_TMP.QA_INSPECT_IND , W_ORA_INVPROD_DS_TMP.REPETITIVE_MFG_IND , W_ORA_INVPROD_DS_TMP.BULK_ITEM_IND , W_ORA_INVPROD_DS_TMP.FORECAST_PERIOD , W_ORA_INVPROD_DS_TMP.MFG_UOM_CODE , W_ORA_INVPROD_DS_TMP.MFG_UOM_NAME , W_ORA_INVPROD_DS_TMP.ISSUE_UOM_CODE , W_ORA_INVPROD_DS_TMP.ISSUE_UOM_NAME , W_ORA_INVPROD_DS_TMP.MANUFACTURING_PLACE , W_ORA_INVPROD_DS_TMP.LOADING_TYPE_CODE , W_ORA_INVPROD_DS_TMP.LOADING_TYPE_NAME , W_ORA_INVPROD_DS_TMP.INT_STORE_LOC_CODE , W_ORA_INVPROD_DS_TMP.INT_STORE_LOC_NAME , W_ORA_INVPROD_DS_TMP.EXT_STORE_LOC_CODE , W_ORA_INVPROD_DS_TMP.EXT_STORE_LOC_NAME , W_ORA_INVPROD_DS_TMP.ACTIVE_FLG , W_ORA_INVPROD_DS_TMP.CREATED_BY_ID , W_ORA_INVPROD_DS_TMP.CHANGED_BY_ID , W_ORA_INVPROD_DS_TMP.CREATED_ON_DT , W_ORA_INVPROD_DS_TMP.CHANGED_ON_DT , W_ORA_INVPROD_DS_TMP.AUX1_CHANGED_ON_DT , W_ORA_INVPROD_DS_TMP.AUX2_CHANGED_ON_DT , W_ORA_INVPROD_DS_TMP.AUX3_CHANGED_ON_DT , W_ORA_INVPROD_DS_TMP.AUX4_CHANGED_ON_DT , W_ORA_INVPROD_DS_TMP.SRC_EFF_FROM_DT , W_ORA_INVPROD_DS_TMP.SRC_EFF_TO_DT , W_ORA_INVPROD_DS_TMP.DELETE_FLG , W_ORA_INVPROD_DS_TMP.DATASOURCE_NUM_ID , W_ORA_INVPROD_DS_TMP.INTEGRATION_ID , W_ORA_INVPROD_DS_TMP.TENANT_ID , W_ORA_INVPROD_DS_TMP.X_CUSTOM , W_ORA_PRODUCT_CTGY_TMP.PROD_HIER1_CODE , W_ORA_PRODUCT_CTGY_TMP.PROD_HIER2_CODE , W_ORA_PRODUCT_CTGY_TMP.PROD_HIER3_CODE , W_ORA_PRODUCT_CTGY_TMP.PROD_HIER4_CODE , W_ORA_PRODUCT_CTGY_TMP.PROD_HIER5_CODE , W_ORA_PRODUCT_CTGY_TMP.PROD_HIER6_CODE
FROM
W_ORA_PRODUCT_CTGY_TMP,
W_ORA_INVPROD_DS_TMP
WHERE
W_ORA_INVPROD_DS_TMP.PRODUCT_ID = W_ORA_PRODUCT_CTGY_TMP.INVENTORY_ITEM_ID(+) AND W_ORA_INVPROD_DS_TMP.INVENTORY_ORG_ID = W_ORA_PRODUCT_CTGY_TMP.ORGANIZATION_ID(+) AND W_ORA_PRODUCT_CTGY_TMP.CATEGORY_SET_ID(+) = 27
]
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8005 : Writer run started.
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8158 :
*****START LOAD SESSION*****
Load Start Time: Wed May 13 22:50:01 2009
Target tables:
W_INVENTORY_PRODUCT_DS
2009-05-13 22:50:01 : INFO : (620 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : RR_4050 : RR_4050 First row returned from database to reader : (Wed May 13 22:50:01 2009)
2009-05-13 22:50:01 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8167 : Start loading table [W_INVENTORY_PRODUCT_DS] at: Wed May 13 22:50:01 2009
2009-05-13 22:50:02 : ERROR : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8229 : Database errors occurred:
ORA-01722: invalid number
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO W_INVENTORY_PRODUCT_DS(PRODUCT_ID,INVENTORY_ORG_ID,PLANT_LOC_ID,PRODUCT_NUM,PRODUCT_DESC,ABC_IND,PLANNER_CODE,PLANNER_NAME,PROCUREMENT_TYPE_CODE,PROCUREMENT_TYPE_NAME,SPC_PROC_TYPE_CODE,SPC_PROC_TYPE_NAME,BUYER_CODE,BUYER_NAME,COMMODITY_CODE,COMMODITY_NAME,COMMODITY_UOM_CODE,COMMODITY_UOM_NAME,PROFIT_CENTER_NUM,PROFIT_CENTER_NAME,REORDER_POINT,SAFETY_STOCK_LEVEL,MIN_LOT_SIZE,MAX_LOT_SIZE,FIXED_LOT_SIZE,MAX_STOCK_LEVEL,LOT_ORDERING_COST,MRP_TIME_FENCE,EXT_PROCURE_TIME,INTERNAL_MFG_TIME,MAX_STORAGE_DAYS,MRP_PROFILE_CODE,MRP_PROFILE_NAME,MRP_TYPE_CODE,MRP_TYPE_NAME,MRP_GRP_CODE,MRP_GRP_NAME,LOT_SIZE_CODE,LOT_SIZE_NAME,BACKFLUSH_IND,QA_INSPECT_IND,REPETITIVE_MFG_IND,BULK_ITEM_IND,FORECAST_PERIOD,MFG_UOM_CODE,MFG_UOM_NAME,ISSUE_UOM_CODE,ISSUE_UOM_NAME,MANUFACTURING_PLACE,LOADING_TYPE_CODE,LOADING_TYPE_NAME,INT_STORE_LOC_CODE,INT_STORE_LOC_NAME,EXT_STORE_LOC_CODE,EXT_STORE_LOC_NAME,ACTIVE_FLG,CREATED_BY_ID,CHANGED_BY_ID,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,SRC_EFF_FROM_DT,SRC_EFF_TO_DT,DELETE_FLG,DATASOURCE_NUM_ID,INTEGRATION_ID,TENANT_ID,X_CUSTOM,PRODUCT_HIER1_CODE,PRODUCT_HIER1_NAME,PRODUCT_HIER2_CODE,PRODUCT_HIER2_NAME,PRODUCT_HIER3_CODE,PRODUCT_HIER3_NAME,PRODUCT_HIER4_CODE,PRODUCT_HIER4_NAME,PRODUCT_HIER5_CODE,PRODUCT_HIER5_NAME,PRODUCT_HIER6_CODE,PRODUCT_HIER6_NAME) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Oracle Fatal Error
2009-05-13 22:50:02 : ERROR : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8425 : ERROR: Writer execution failed.
2009-05-13 22:50:02 : ERROR : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8076 : Writer run terminated. [Commit Error]
2009-05-13 22:50:02 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8333 : Rolling back all the targets due to fatal session error.
2009-05-13 22:50:02 : ERROR : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : CMN_1022 : Database driver error...
CMN_1022 [
ORA-24795: Illegal ROLLBACK attempt made
Database driver error...
Function Name : Commit
Database driver error...
Function Name : Rollback]
2009-05-13 22:50:02 : ERROR : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8183 : Rollback failed for the target [W_INVENTORY_PRODUCT_DS]
2009-05-13 22:50:02 : ERROR : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8081 : Writer run terminated. [Error in loading data to target table [W_INVENTORY_PRODUCT_DS]]
2009-05-13 22:50:02 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8168 : End loading table [W_INVENTORY_PRODUCT_DS] at: Wed May 13 22:50:02 2009
2009-05-13 22:50:02 : INFO : (620 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8035 : Load complete time: Wed May 13 22:50:02 2009
LOAD SUMMARY
============
WRT_8036 Target: W_INVENTORY_PRODUCT_DS (Instance Name: [W_INVENTORY_PRODUCT_DS])
WRT_8038 Inserted rows - Requested: 24 Applied: 0 Rejected: 0 Affected: 0
2009-05-13 22:50:02 : INFO : (620 | WRITER_1__1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : WRT_8043 : ****END LOAD SESSION*****
2009-05-13 22:50:02 : ERROR : (620 | TRANSF_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6085 : The transformation [W_INVENTORY_PRODUCT_DS] encountered fatal error. Terminating session.
2009-05-13 22:50:02 : ERROR : (620 | TRANSF_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6085 : The transformation [EXP_Inventory_Product] encountered fatal error. Terminating session.
2009-05-13 22:50:02 : ERROR : (620 | TRANSF_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6085 : The transformation [EXP_Inventory_Product] encountered fatal error. Terminating session.
2009-05-13 22:50:02 : ERROR : (620 | TRANSF_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6085 : The transformation [SQ_W_ORA_INVPROD_DS_TMP] encountered fatal error. Terminating session.
2009-05-13 22:50:02 : ERROR : (620 | TRANSF_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6085 : The transformation [SQ_W_ORA_INVPROD_DS_TMP] encountered fatal error. Terminating session.
2009-05-13 22:50:02 : ERROR : (620 | TRANSF_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6085 : The transformation [SQ_W_ORA_INVPROD_DS_TMP] encountered fatal error. Terminating session.
2009-05-13 22:50:02 : DEBUG : (620 | TRANSF_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : DBG_21511 : TE: Fatal Transformation Error.
2009-05-13 22:50:02 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : 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 [SQ_W_ORA_INVPROD_DS_TMP] 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 [SQ_W_ORA_INVPROD_DS_TMP] 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_INVENTORY_PRODUCT_DS] has completed. The total run time was insufficient for any meaningful statistics.
2009-05-13 22:50:02 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24005 : PETL_24005 Starting post-session tasks. : (Wed May 13 22:50:02 2009)
2009-05-13 22:50:02 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24029 : PETL_24029 Post-session task completed successfully. : (Wed May 13 22:50:02 2009)
2009-05-13 22:50:02 : INFO : (620 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6018 : Session [SDE_ORA_InventoryProductDimension_Derive] run completed with [0] row transformation errors.
2009-05-13 22:50:02 : INFO : (620 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24002 : Parallel Pipeline Engine finished.
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : PETL_24013 : Session run completed with failure.
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6022 :
SESSION LOAD SUMMARY
================================================
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6252 : Source Load Summary.
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : CMN_1740 : Table: [SQ_W_ORA_INVPROD_DS_TMP] (Instance Name: [SQ_W_ORA_INVPROD_DS_TMP])
Output Rows [1288], Affected Rows [1288], Applied Rows [1288], Rejected Rows [0]
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6253 : Target Load Summary.
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : CMN_1740 : Table: [W_INVENTORY_PRODUCT_DS] (Instance Name: [W_INVENTORY_PRODUCT_DS])
Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6023 :
===================================================
2009-05-13 22:50:02 : INFO : (620 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_xpone : TM_6020 : Session [SDE_ORA_InventoryProductDimension_Derive] completed at [Wed May 13 22:50:02 2009]
Edited by: tigger on May 15, 2009 9:45 AM