ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Writing SQL counts using Jython

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.

image

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.

image

Download the Codes here

Download XML

3 Comments

  1. Hi,

    I too use jython in my current project which is in ODI 12c. I have observed that sometimes the jython code written inside ODI procedures gets stored in a improper format like all the code in a single link.
    Is it a bug in ODI or is there anyway to overcome this issue? Please help me to resolve this issue.

  2. Hi,

    I have requirement where I have to write file with multiple headerline. we are using ODI10g. let’s say I have department table as shown below

    DeptId EmpId EmpName
    1 123 Santosh
    1 124 Harish
    2 211 Rane
    3 311 Sailesh
    3 312 Ramesh

    Now I want to write a file like below

    Header deptId1, Emp Count 2
    Dep 1, Emp 123, Emp name Santosh
    Dep 1, Emp 124, Emp name Harish
    Header deptId2, Emp Count 1
    Dep 2, Emp 211, Emp name Rane
    Header deptId3, Emp Count 2
    Dep 3, Emp 311, Emp name Sailesh
    Dep 3, Emp 312, Emp name Ramesh

    How can I achieve this?

    Thanks in Advance

Leave a Reply

Required fields are marked *.