Forum Stats

  • 3,838,328 Users
  • 2,262,355 Discussions
  • 7,900,585 Comments

Discussions

Data load Rule for BefImport script getting failed because of Error Code ORA-01858

ORAHYPFDM
ORAHYPFDM Member Posts: 372 Blue Ribbon
edited Feb 20, 2016 2:24AM in Financial Data Management

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

Tagged:

Best Answer

  • WVanSluys1
    WVanSluys1 Member Posts: 674
    edited Feb 5, 2016 4:02PM Answer ✓

    Nishant

    quick Question....What version are you using.  the Latest Patch now has Universal Data Adapter which allows for direct import of tables and views without needing to use Open Interface table to stage data

    if you are not on current .100 patch then you should look at the data types for the OpenInterface table... you may see the issue.  I am guessing it is the YEAR or PERIOD_NUM column giving you the fits

    if you are running your code in Eclipse before deploying i would suggest printing our your insertStmt SQL and then try to run it in SQLDeveloper.

    pastedImage_0.png

    Hope this helps

    Wayne

Answers

  • WVanSluys1
    WVanSluys1 Member Posts: 674
    edited Feb 5, 2016 4:02PM Answer ✓

    Nishant

    quick Question....What version are you using.  the Latest Patch now has Universal Data Adapter which allows for direct import of tables and views without needing to use Open Interface table to stage data

    if you are not on current .100 patch then you should look at the data types for the OpenInterface table... you may see the issue.  I am guessing it is the YEAR or PERIOD_NUM column giving you the fits

    if you are running your code in Eclipse before deploying i would suggest printing our your insertStmt SQL and then try to run it in SQLDeveloper.

    pastedImage_0.png

    Hope this helps

    Wayne

  • ORAHYPFDM
    ORAHYPFDM Member Posts: 372 Blue Ribbon
    edited Feb 20, 2016 2:24AM
This discussion has been closed.