Hi everyone , few post back we have shown how to get the count in Operator but there are many time you may want to get the count of records by email once the data got loaded. To solve this we have written a simple jython procedure to get and count and attach the file as Email and set up the process

Here is a simple Jython Procedure to  do that  .

import string
import java.sql as sql
import java.lang as lang
import re
sourceConnection = odiRef.getJDBCConnection("SRC")
output_write=open('c:/count_write.txt','w')
sqlstring = sourceConnection.createStatement()
print >> output_write, " The counts are .... 'n'"
#---------------------------------------------------------------
# QUERY 1 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_TEMP'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='n'+str(sqlstmt)+'nn'+str(count)

print >> output_write ,rs

#---------------------------------------------------------------
# QUERY 2 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='ODI_WORK'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='n'+str(sqlstmt)+'nn'+str(count)

print >> output_write ,rs

#---------------------------------------------------------------
# QUERY 3 COUNT
#---------------------------------------------------------------

sqlstmt="select count(*) as rowcount from all_tables where owner='SYSTEM'"
result=sqlstring.executeQuery(sqlstmt)
while result.next():
count=result.getInt("rowcount")
rs='n'+str(sqlstmt)+'nn'+str(count)

print >> output_write ,rs

sourceConnection.close()
output_write.close()

and you get the output in similar form.

Just replace the  sql count query according to your requirement.

and provide the  schema in the ‘Command on Source ‘ which have the select permission on the required table for which the counts are being retrieved.

Download the Codes here

Download XML