ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Get the Error Message of any Failed ODI objects inside Package

When we have multiple Interface, Variable  and other ODI objects inside the Child Package  and when such Child Package Fails we get the Error message ‘The scenario did not end properly’  and we need to look into the operator .

Here is a simple code  and process which can look into the Child Session and get the correct error message and also later we can capture the same in the Error Log .

 image

Create a Global Variable called “ GET_ERROR_MSG” and paste the following codes ,  so you can call and use it for multiple package.

SELECT ' The  '||CASE
WHEN SSS.STEP_TYPE='F' THEN 'INTERFACE'
WHEN SSS.STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN SSS.STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN SSS.STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN SSS.STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN SSS.STEP_TYPE='T' THEN 'PROCEDURE'
WHEN SSS.STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN SSS.STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN SSS.STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN SSS.STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN SSS.STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN SSS.STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN SSS.STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN SSS.STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END||' '||SSS.STEP_NAME||
' , has failed due to - '||
SNET.TXT
FROM <%=odiRef.getObjectName("SNP_SESSION")%> SS,
<%=odiRef.getObjectName("SNP_SESS_STEP")%> SSS,
<%=odiRef.getObjectName("SNP_EXP_TXT")%> SNET
WHERE SSS.SESS_NO=SS.SESS_NO
AND SNET.I_TXT=SS.I_TXT_SESS_MESS
AND SNET.TXT_ORD=0
AND SS.PARENT_SESS_NO ='<%=odiRef.getPrevStepLog("SESS_NO")%>'

 
[ Note :select the Work repository schema in the variable so that getobjectname can calculate accordingly the values, if in case you are using other schema which have select permission on work Repository schema , replace <%=odiRef.getObjectName(“”)%> with the required schema name. ]

In the first step call an OdiOutFile and uncheck Append option so that new File is created for a daily run  with  Header and Date .

image

Call the Child package and call the Variable   and  join the KO step ( red) to the Variable so that when ever the Child Package fails only then the variable is called else process the next child Package.

In the next step , call the OdiOutFile and mention the path to File mentioned in Step 1 and in the Text mention the variable name and keep repeating the steps for multiple child packages.

image

This way you can capture all the Failed ODI objects Error Messages into Single Log and send them as an attachment through OdiSendMail.

 image

3 Comments

  1. I dont have any data in snp_exp_txt table, please let me know how i can get the data in the table.

  2. Hello Christina,

    the code is a ‘trap’ :D. Make sure that the scenario you execute before the GET_ERROR_MESSAGE fails internally.
    Do not execute a non existent scenario ‘just to test it’. If the scenario does not start the refresh of the variable will fail.
    Also the variable should be TEXT.

    Regards,
    Alex Pavel

  3. Hello,

    I created a variable to be used in my pack charge with the following commands:

    SELECT ‘ A ‘ || CASE
    WHEN SSS.STEP_TYPE = ‘F’ THEN
    ‘INTERFACE’
    WHEN SSS.STEP_TYPE = ‘VD’ THEN
    ‘DECLARAÇÃO DE VARIÁVEL’
    WHEN SSS.STEP_TYPE = ‘VS’ THEN
    ‘INCREMENTO DA VARIÁVEL’
    WHEN SSS.STEP_TYPE = ‘VE’ THEN
    ‘AVALIAÇÃO DE VARIÁVEL’
    WHEN SSS.STEP_TYPE = ‘V’ THEN
    ‘ATUALIZAÇÃO DA VARIÁVEL’
    WHEN SSS.STEP_TYPE = ‘T’ THEN
    ‘PROCEDURE’
    WHEN SSS.STEP_TYPE = ‘OE’ THEN
    ‘COMANDO DO SO’
    WHEN SSS.STEP_TYPE = ‘SE’ THEN
    ‘FERRAMENTA DO ODI’
    WHEN SSS.STEP_TYPE = ‘RM’ THEN
    ‘ENGENHARIA REVERSA DO MODELO’
    WHEN SSS.STEP_TYPE = ‘CM’ THEN
    ‘CHECK MODELO’
    WHEN SSS.STEP_TYPE = ‘CS’ THEN
    ‘CHECK SUB MODELO’
    WHEN SSS.STEP_TYPE = ‘CD’ THEN
    ‘CHECK DATA STORE’
    WHEN SSS.STEP_TYPE = ‘JM’ THEN
    ‘JOURNALIZE MODEL’
    WHEN SSS.STEP_TYPE = ‘JD’ THEN
    ‘JOURNALIZE DATA STORE’
    END || ‘ ‘ || SSS.STEP_NAME || ‘ , falhou devido a – ‘ || SNET.TXT
    FROM SS,
    SSS,
    SNET
    WHERE SSS.SESS_NO = SS.SESS_NO
    AND SNET.I_TXT = SS.I_TXT_SESS_MESS
    AND SNET.TXT_ORD = 0
    AND SS.SESS_NO =
    AND ROWNUM = 1

    Setting the variable for the schema I’m using and defines it as Last Value, but when I run it returns me the following error:

    java.lang.Exception: Error: No value to affect to this variable for DefDate:2010-08-31 02:40:52.0
    DefN:null
    DefV:null
    IndStore:L
    ITxtDefT:1307001
    ITxtVar:1306001
    SessNo:298001
    VarDatatype:T
    VarName:GLOBAL.vg_RETORNA_ERRO

    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.execStdOrders(SnpSessTaskSqlV.java)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.treatTaskTrt(SnpSessTaskSqlV.java)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.treatTask(SnpSessTaskSqlV.java)

    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

    at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)

    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

    at com.sunopsis.dwg.cmd.e.i(e.java)

    at com.sunopsis.dwg.cmd.g.y(g.java)

    at com.sunopsis.dwg.cmd.e.run(e.java)

    at java.lang.Thread.run(Unknown Source)

    The return of the variable is inserted in a file. Txt

    Thanks!

Leave a Reply

Required fields are marked *.