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 ]

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

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

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…