5 Replies Latest reply on Jul 23, 2017 12:13 PM by 1006942

    FDMEE and open_cursor issue

    1006942

      Dear All,

      I have been deveopping a BefImport.py envent script, and I am facing a strange issue.

      The script read a file from a network location, make some operations and manipulate data and then inserts data in AIF_OPEN_INTERFACE table.

      When i run the import of a small file, every thing is OK.

       

      When the file contains much more rows the problem occures. the error message logged in the output log file is  "The script has failed to execute".

      The ODI detailed message is :ORA-01000 maximum cursors exceeded

       

      For example : When the configuration of oracle instance is:

      Open_cursor = 300, The import process inserts 297 rows on AIF_OPEN_INTERFACE and then falls in error

      Open_cursor = 1000, the import process inserts 996 rows on AIF_OPEN_INTERFACE and then falls in error

      Open_cursor = 5000 (as the technical recommandations), the import process inserts 4996 rows on AIF_OPEN_INTERFACE and then falls in error

       

      How can I do to manage files with more than 50K rows? is it a configuration in FDMEE to limte the number of the opened cursors?

       

      The script is very simple:

       

      read_file = codecs.open(file, 'r', encoding='utf-8')

      for line in read_file:

        #operations and initialize variables....

       

        strSQL = """

        Insert into AIF_OPEN_INTERFACE

        (BATCH_NAME, YEAR, PERIOD, PERIOD_NUM, CURRENCY, COL01, COL02, COL03, COL04, COL05, COL06, COL07, COL08, COL09, COL10, COL11,COL12,COL13,AMOUNT)

        values

        (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

        """

        params = ["BATCH_" + fdmContext["RULENAME"], sYear, strPeriod, sPeriod, sCur, sAccount, sEntity, sICP, sUD1,sUD2, sUD3, sUD4, sUD5, sUD7,sUD8, sUD9,sUD10, sUD11, sAmount] 

        fdmAPI.executeDML(strSQL, params, False)

        fdmAPI.commitTransaction()

      read_file.close()

       

      The configuration is the following:

      - FDMEE 11.1.2.4

      - Oracle 12C on Windows

      - FDMEE recommandations open_cursors, sessions cache_cursors ..etc configured.

       

       

      Thank you for your help,

      Best regards,