ODI

37 Comments

  1. Hi,
    i am working in odi with conversion of txt file into database table but i m facing one issue
    -when i worked with km then for that i have to select lkm as file to sql but it’s not cuming
    what can be the problem..

  2. hi,
    actually i have a requirement where a user enters his details ohto a webpage and then after clicking a submit button the the URL is generated which contains the entered information . Now the thing is i want to enter the details into some xyz table. so how shud i tackle this situation. do i need to configure oracle weblogic server on my side ??
    technologies i can use are ODI 11g , oracle weblogic server and java for this case.
    pls provide a better solution . and steps .

  3. Hi Ranjan,
    we started to implement ODI this year and we are almost done with development. I need to move all the objects from Dev to UAT box soon. what is your recommendation on how to go about with exports. BTW our odi version is 11.1.1.3
    Thanks in advance.

    • Hi Sridhar,
      Thanks for visiting my blog. If I would have been in your case then i must have chosen promoting scenarios to UAT and then the same scenario to PRODUCTION. Also after promoting scenarios to UAT I must have taken the backup of the complete project in another repository. Because If i will promote the complete project to UAT then i might face issues related to missing reference as well as issue related to testers (Tester might change codes unknowingly).
      Thanks.

  4. can i assign variable to variable in odistratscen?
    odistartscen scename=xxxxx -project.variable=#variablename

    i am trying the above syntax it is giving error

    • If you are starting from command prompt i believe it wont work. If you are using the OdiStartScen tool in the odi package then it must work. Keep in mind that you have put the variable just before the startScenario step in set mode or refresh mode. And then you can pass this variable to the variable used in startScen command.

  5. Hi Ranjan,
    we are really havng this one issue with sqlldr and I am unable to debug the problem and this is holding our implementation to UAT.
    I have previously posted a question on the forum last week and did not get a good response.

    can you please help.

    https://forums.oracle.com/forums/forum.jspa?forumID=374&start=0

    here is the issue:
    I have scroll thru forums for resolution of similar issue but no luck. Here is the problem this is working fine in ODI version 11.1.1.5, Except the file is mapped to local drive.
    Now below is the error message from ODI 11.1.1.3, Trying to use LKM File to Oracle(SQLLDR) and here is the error

    ODI-1226: Step LD_PARTNER_USING_SQLLDR fails after 1 attempt(s).
    ODI-1240: Flow LD_PARTNER_USING_SQLLDR fails while performing a Loading operation. This flow loads target table DRM_USER_PARTNER_NEW.
    Caused By: org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File “”, line 33, in ?
    Load Error: See \\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile/LD_USER_PARTNER.log for details

    here is the generated code from call sqlldr session task

    import java.lang.String
    import java.lang.Runtime as Runtime
    from jarray import array
    import java.io.File
    import os
    import re

    def reportnbrows():

    f = open(r”\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile/LD_USER_PARTNER.log”, ‘r’)
    try:
    for line in f.readlines():
    if line.find(“MAXIMUM ERROR COUNT EXCEEDED”)>=0 :
    raise “MAXIMUM ERROR COUNT EXCEEDED”, “See %s for details” % logfile
    finally:
    f.close()

    ctlfile = r”””\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile/LD_USER_PARTNER.ctl”””
    logfile = r”””\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile/LD_USER_PARTNER.log”””
    outfile = r”””\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile/LD_USER_PARTNER.out”””
    oracle_sid=”
    if len(‘d1odi1’)>0: oracle_sid = ‘@’+’d1odi1’

    loadcmd = r”””D:\Oracle\product\11.2.0\client_1\bin\sqlldr ‘MDM_STAGE/%s’ control=’%s’ log=’%s’ > “%s” “”” % (oracle_sid,ctlfile, logfile, outfile)

    rc = os.system(loadcmd)

    if rc 0 and rc 2:
    raise “Load Error”, “See %s for details” % logfile

    if rc==2:
    reportnbrows()

    what am i missing?

    then i tried to run sqlldr straight with hardcoded data and same error 🙁

    please help.
    Thanks.

      • I have changed it to forward slash and see error message below. It never created the log ” \\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile\LD_USER_PARTNER.log” but both .ctl and .out file gets created every time.

        org.apache.bsf.BSFException: exception from Jython:
        Traceback (innermost last):
        File “”, line 33, in ?
        Load Error: See \\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile\LD_USER_PARTNER.log for details

        import java.lang.String
        import java.lang.Runtime as Runtime
        from jarray import array
        import java.io.File
        import os
        import re
        import javaos

        def reportnbrows():

        f = open(r”\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile\LD_USER_PARTNER.log”, ‘r’)
        try:
        for line in f.readlines():
        if line.find(“MAXIMUM ERROR COUNT EXCEEDED”)>=0 :
        raise line
        finally:
        f.close()

        ctlfile = r”””\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile\LD_USER_PARTNER.ctl”””
        logfile = r”””\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile\LD_USER_PARTNER.log”””
        outfile = r”””\\Cdpwa01a0600\e$\Hyperion\DRM\Loadfile\LD_USER_PARTNER.out”””

        loadcmd = r”””sqlldr MDM_STAGE/xxxxxx@d1odi1 control=”%s” log=”%s” > “%s” “”” % (ctlfile, logfile, outfile)

        rc = os.system(loadcmd)

        if rc 0 and rc 2:
        raise “Load Error”, “See %s for details” % logfile

        if rc==2:
        reportnbrows()

  6. Hi Bhabani,
    Back again for a query actually I am invoking a shell script from odi with the help of odiOsCommand. The shell script is successfully called.But I need to return a value from shell script to the odi. How to do that ?
    In details:-
    The script is Sum.sh.I pass two variables in the command line.The script is successfully executed but I want to store the “result of the summation” inside a odi variable.How to do it?
    Please Answer

    Thanks
    Anindya

  7. Hi Ranjan,

    Need your suggestions for the questions below.

    Question:1

    I have a flatfile(1 million records) that I am loading into Oracle table using LKM File to sqlldr and IKM SQL Incremental Update.
    I made sure target table is empty on every load. so here is the problem, whenever I load the flatfile into the target table, the order of records in the
    table does not match the order of the file records in the flatfile. I don’t have seqnum nor order by columns from in the source file.
    Today I use oracle sqlldr to load the file on daily basis and How would you handle this file?

    Question :2

    Can I run a jython script in odi with out creating a interface and using package tools? If so how?

    Thanks in advance.

    Sridhar.

  8. HI,
    while loading the file using the lkm file to sqlldr i am getiing an error like:org.apache.bsf.BSFException: exception from Jython:
    Traceback (most recent call last):
    File “”, line 33, in
    Load Error: See /apps/odi/NSG/Inbound/WFADI/RAW2.log for details

    Couls u pls help on this

  9. hi,

    I am new in ODI I am just trying load sql table to oracle but in target table data is not load and in oprator the errors are ODI 1226
    ODI 1240
    ODI 1228

    • Welcome to ODI World. Open the step where you see a red cross. Double click on this and go to code tab. Copy and pest whatever code you see there. Also tell me the step name.

  10. Hello,
    I have done the table to flat file integration.i have modify the scenario that i have add the variable and witha that variable i pass the sysdata. that is overwrite the flat file name.
    i have created one package
    and have declare one date variable
    the package name is dyanamic file name
    variable name isfilw name
    so get this error
    2014-04-10 10:43:25.514 WARNING ODI-1134 Agent Internal encountered an error: ODI-1226: Step FileName fails after 1 attempt(s).
    2014-04-10 10:43:25.514 ERROR ODI-1217 Session Dyanamic File Name (105005) fails with return code 7000.

    • Actually i didnt understand your question. You want to generate a file having the date as part of the filename . Is it? Can you exaplain bit more like what you have done so far, where you are putting the variable, what was the expression bla bla..

      • i want to transform load data to the datafile and that file name wants the sysdate so what are the steps i have to follow guide me

        • You better load data to file having fixed name and then move that file to another location. Append the refresh variable to the filename while moving to other directory.

          Put the refresh query as select to_char(sysdate,’yyyymmdd’) from dual

  11. hello,

    this is the error is
    java.text.ParseException: Unparseable date: “”
    at java.text.DateFormat.parse(DateFormat.java:337)
    at oracle.odi.runtime.agent.invocation.request.support.OdiDateFormat.parse(OdiDateFormat.java:40)
    at com.sunopsis.dwg.dbobj.SnpVarSess.setValue(SnpVarSess.java:963)
    at com.sunopsis.dwg.dbobj.SnpVarSess.setValue(SnpVarSess.java:667)
    at oracle.odi.runtime.agent.execution.var.AssignVariableExecutor.execute(AssignVariableExecutor.java:74)
    at oracle.odi.runtime.agent.execution.var.AssignVariableExecutor.execute(AssignVariableExecutor.java:1)
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.processTask(SnpSessTaskSqlV.java:410)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.treatTask(SnpSessTaskSqlV.java:240)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:580)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
    at java.lang.Thread.run(Thread.java:662)

  12. thank you i have solve that problem i have created one variable and that i pass to the data store of the flat file using #variable name

Leave a Reply

Your email address will not be published.


*