Hello Guys,
I am trying to load data from Views created in EBS to HFM using FDMEE witht he help of BefImport Script. For this I have written below script:
_________________________________________________________________________________________________________________________________________________
import java.sql as sql
import sys
batchName = "Batch_" + str(fdmContext["LOCNAME"])
insertStmt = ""
stmtRS = ""
if (fdmContext["LOCNAME"] == "LOC_OIA_FA_CORP1"):
insertStmt = """
INSERT INTO AIF_OPEN_INTERFACE (
BATCH_NAME
,YEAR
,PERIOD
,PERIOD_NUM
,CURRENCY
,COL04
,COL05
,COL07
,COL08
,COL09
,COL10
,COL11
,AMOUNT
) VALUES (
?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
,?
)
"""
sourceConn = sql.DriverManager.getConnection("jdbc:oracle:thin:@ebsr12dbcnvgl.abc.com:1541:avc", "pppp", "nnnnn");
# Limiting number of rows to 5 during the test runs.
selectStmt = "SELECT TEMP_VIEW.PERIOD_YEAR,TEMP_VIEW.PERIOD_NAME,TEMP_VIEW.PERIOD_NUM,TEMP_VIEW.CURRENCY_CODE,TEMP_VIEW.SEGMENT1,TEMP_VIEW.SEGMENT2,CONCAT(TEMP_VIEW.SEGMENT3,TEMP_VIEW.SEGMENT4) AS SEGMENTX,TEMP_VIEW.SEGMENT5,TEMP_VIEW.SEGMENT6,TEMP_VIEW.SEGMENT7,TEMP_VIEW.SEGMENT8,TEMP_VIEW.NETBAL FROM TEMP_VIEW WHERE NETBAL IS NOT NULL"
stmt = sourceConn.prepareStatement(selectStmt)
stmtRS = stmt.executeQuery()
batchName = "Batch_" + str(fdmContext["LOCNAME"])
while(stmtRS.next()):
params = [ batchName, stmtRS.getString("PERIOD_YEAR"),
stmtRS.getString("PERIOD_NAME"),
stmtRS.getString("PERIOD_NUM"),
stmtRS.getString("CURRENCY_CODE"),
stmtRS.getString("SEGMENT1"),
stmtRS.getString("SEGMENT2"),
stmtRS.getString("SEGMENTX"),
stmtRS.getString("SEGMENT5"),
stmtRS.getString("SEGMENT6"),
stmtRS.getString("SEGMENT7"),
stmtRS.getString("SEGMENT8"),
stmtRS.getBigDecimal("NETBAL") ]
fdmAPI.executeDML(insertStmt, params, False)
fdmAPI.commitTransaction()
stmtRS.close()
stmt.close()
sourceConn.close()
_________________________________________________________________________________________________________________________________________________
But my DLR gets failed. When I checked ODI log for this, I have found that it is saying: problem in stmtRS = stmt.executeQuery() and showing and error code:
java.sql.SQLDataException : java.sql.SQLDataException : ORA- 01858 : found a non-numeric character where a numeric was expected.
What will be the problem? Kindly help.
Thanks
Regards
Nishant