ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Error Records Log

Hi every one…

In the last few posts we have shown how to display the SQL results in Operator , fetch the failed objects errors message in Package and various other techniques. Today we want to cover another small scripts which can enable you fetch details of the Interface that have error  out because of PK , FK ,Not Null constraints etc and goes to the Error table.

This scripts creates a File and dumps all the information by reading from SNP_CHECK_TAB  table and finally file can be added to OdiSendMail and send to the Administrator or Developers so that they can know which interface got error  records, so they can do the needful and also in daily load we fail to see all these smaller details especially we have hundreds of interfaces.

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/snp_check_tab.txt','w')
sqlstring = sourceConnection.createStatement()
#---------------------------------------------------------------------------
output_write.write("The Errored Interface of today's (<%=odiRef.getSysDate( )%>)
run are .... n")
output_write.write("----------------------------------------------------------- nn")
#---------------------------------------------------------------------------
sqlstmt="SELECT 'Errored Interface t- '||SUBSTR(ORIGIN,INSTR(ORIGIN,')')+1,
LENGTH(ORIGIN))||'nError Message tt- '||ERR_MESS||'nNo of Errored Records t- '||
ERR_COUNT AS OUTPUT FROM  ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)"
result=sqlstring.executeQuery(sqlstmt)
rs=[]
while (result.next()):
      rs.append(str(result.getString("output")+'t'))

res='nn'.join(map(string.strip,rs))
print >> output_write, res

sourceConnection.close()
output_write.close()

[Note – In the above scripts please change the File path and the Schema(Work Schema ) name according to your respective Environment ]

image

Provide the Technology and Schema of your work Schema or required schema which can access the SNP_CHECK_TAB and provide the code in Command on Target

image

and for every run you will get the sample output as shown below.

image

Attach the File to OdiSendMail and get the daily Error Interface , Message and Records detail .

Download the Codes

Comments are alwasys welcome!

See you soon…

6 Comments

  1. While scanning my files on to ODI, I am not able to see what I printed. How do I fix this?

  2. Hi,
    I have an issue in log file name. I’m loading data into Hyperion Financial Management. In the IKM of the SQL to HFM data, we have an option of log file enabled. I made it true and gave the log file name as ‘HFM_dataload.log’. After executing the interface when I navigate in to that log folder and view the log file, that file is blank. Also a new file ‘HFM_dataloadHFM6064992926974374087.log’ is cerated and the log details are displayed in it. Since I have to automate the process of picking up the everyday log file and mail it to the users,
    * I need the log details to be displayed in the specified log name i.e. ‘HFM_dataload.log’
    * Also I was not able to perform any action(copy that newly generated log file into another or send that file in mail), since I’m not able to predict the numbers generated along with the specified log file name.

    Kindly help me to overcome this issue.

    Thanks in advance.

    • Please look into the KM how ODI is creating the number in HFM_dataloadHFM6064992926974374087.log and through that same logic u can pickup the Log file or u can add the command to rename to what ever file u wish to create and use that in your email.

      Please let us know if you need any other help.

      Thanks
      Kshitiz Devendra

  3. hi ,

    i did the same thing as you said in the above one , but the error is showing that table or view does not exist

    • Here SELECT ‘Errored Interface t- ‘||SUBSTR(ORIGIN,INSTR(ORIGIN,’)’)+1, LENGTH(ORIGIN))||’nError Message tt- ‘||ERR_MESS||’nNo of Errored Records t- ‘|| ERR_COUNT AS OUTPUT FROM ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)”

      replace the schema name ODI_TEMP with your work schema name where the SNP_CHECK_TAB exist and please re run again .

      Thanks

Leave a Reply

Required fields are marked *.