ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Displaying SQL results in Operator

Few posts back i have shown ,how to display the value of the variable using Jython and today i am showing how to display  SQL  Query result  in the operator.

— Displays the Sql results of query into Operator –

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
result=sqlstring.executeQuery("select table_name,owner from all_tables where owner='ODI_TEMP'")
i=0
rs=[]
while (result.next()):
    rs.append(str(i)+'t'+str(result.getString("table_name"))+'tt'+str(result.getString("owner"))+' , ')
    i=i+1

res='n'.join(map(string.strip,rs))
raise res
sourceConnection.close()

In  sourceConnection = odiRef.getJDBCConnection(“SRC”), the connection details are retrieved from ODI , to do so in the ‘ command on  Source tab’  – Select the Technology and required Schema so that ODI can retrieve the connection details.

image

result=sqlstring.executeQuery(“select table_name,owner from all_tables where owner=’ODI_TEMP'”)

# Enter the query between the two double quotes or replace the above query .

rs.append(str(i)+’t’+str(result.getString(“table_name”))+’tt’+str(result.getString(“owner”))+’ , ‘)

# For every column you mention in the above query – get them under result.getString(“Column_Name”)

# as for my example i am having two column so iam calling getString twice , similarly do so for every column

# called in the Select statement

In the Target technology select  ‘  Jython ‘

image

Enable  the ‘ Ignore Errors’ option.

Sample Output

image

– Codes to display the Row count –

import string
import java.sql as sql
import java.lang as lang
sourceConnection = odiRef.getJDBCConnection("SRC")
sqlstring = sourceConnection.createStatement()
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)
raise rs
sourceConnection.close()

Sample Output

image

5 Comments

  1. What can i do getOther? Where list of types they can be get?

  2. Guys
    I suggest that it would good to let ODI manage the JDBC connection,, don’t go creating one yourself, use the odiRef,getJDBCConnection() method, then ODI will handle the connection automatically for you.

  3. Raising an exception just to print out stuff seems crude, shouldn’t a simple println work?

    In second example you don’t need to iterate over the whole resultset, just do this:
    resultset.last()
    rowcount = resultset.getRow()

    Thanks for the awesome blog!

    • Thanks Markus for your suggestion , the reason i am calling a raise command is that using raise i can display the output as an exception in the ODI operator .

      Secondly i wanted to maintain the uniformity in the above two codes so i have used while loop , moreover just getting single line the second program can also be called like this too .

      result=sqlstring.executeQuery(sqlstmt)
      result.next()
      count=result.getInt(“rowcount”)
      rs=’n’+str(sqlstmt)+’nn’+str(count)
      raise rs

Leave a Reply

Required fields are marked *.