There are few days that we don’t have new posts around here… We are pretty busy in the business in the past days!!!
But, here we are with a new post that we, personally, think will be really helpful to those who like “deep” programming in ODI.
I hope you all enjoy it!
When it comes to ODI sooner or later we might have to deal with Jython and some one who have used Java would find that easy ,but not for every one . Considering that we thought of writing this post in understanding how to deal with Jython in relation to ODI . We are not going to teach you Jython 🙂
Generally most of the uses of Jython in ODI is either to capture and raise error in the operator, run OS command and do the appropriate if and else condition , reading and processing records for RDBMS , File processing based on different logic and so on.
When we use jython like java we need to import different modules , Example
we can also import java libraries something like you have seen few of the past codes
import java.lang.sql as sql
import java.lang.io as io
Lets look on few codes which we might use more and more.
Reading , Writing , Re-writing the file
There are different format in jython to process files, but we personally like this format
Here in the above syntax, we can change the parameters of file by
r – only for reading
r+ – re writing into the same file
w – writing to a new file
When we use W (writing) on an existing file the whole file gets flushed or cleared out,so be careful when pointing to any file.
There are again different format to read the lines of the Files either complete or line by line.
We can write codes using the list format to process records into the memory but if we are dealing many millions of records calling and storing into memory and processing it can throw memory error something like java.lang.memory exception . Keeping that in mind , we write codes to read line by line and process accordingly.
Reading Line by Line
srcfile=open('file_name','r') first_line=srcfile.readline() - Reading First Line second_line=srcfile.readline() - Reading Second Line
This way when we need to read just one line we can use to readline() command . The pointer of the line keeps moving one line to next line.
Lets say if we want to keep reading all the lines
srcfile=open('file_name','r') line=srcfile.readline() i=0 while line: print line i+=1 line=srcfile.readline() print 'Total Number of Lines', i srcfile.close()
This above simple program reads all the line one by and incremented i for each line it read, so that at the last we are also print the value of ‘i ,denoting the number of lines in File.
Let me cover step by step
srcfile=open('file_name','r') # opening the File in Read only mode line=srcfile.readline() # Reading the First Line i=0 while line: # while loop started to read the lines one by one print line # printing the line . Please note that when ever you write codes under while or for loop ,you need to write after a tab space else the codes wont get executed. i+=1 # increment the value of i by 1 line=srcfile.readline() #reading the next line of the File print 'Total Number of Lines', i # print the value of i srcfile.close() #Always close the file which you have open for read , write or any other mode else the file is open by memory in jython and unnecessary data is lying in its #memory.
Reading all the Lines at one shot
srcfile=open('file_name','r') lines=srcfile.readlines() print lines srcfile.close()
Here all the lines of the Files are read a list and stored in the memory error, so in case if we have huge record it can throw memory . To solve either go for Record by record processing which can be a little time consuming or increase the Java memory to handle the processing.
Here readlines() reads all the lines of the source file and stores in the form of array and pass it to variable lines.
Writing the data into File.
There are different ways we can write the records into File .
If you are writing a string , you can use the write(string ) to write into file
output_write=open('<file_name>','w') str1='This is a sample program' output_write.write(str1) output_write.close()
In case you are planning to write multiple lines say array or list, you can use the writelines()
source_file=open('<file_name>','r') output_write=open('<file_name>','w') read_lines=source_file.readlines() output_write.writelines(read_lines) source_file.close() output_write.close()
You can also use the java BufferedWriter.
import java.io as io writer=io.BufferedWriter(io.OutputStreamWriter(io.FileOutputStream('<file_name_to_be_writtne>'))) writer.write('string or content') writer.flush() writer.close()
One of the fastest way to write into File is to store data into Variable and write them once rather than in a while or for loop that way the overall time is reduced . This idea is great when you running your codes in Server and as we have high memory to process and store larger records
If you ever want to go to the First line of the file use seek(0) command , so in the above program if i want to go to first line it will be
srcfile.seek(0) #( seek(zero))
t and n
t moves the pointer to include a tab space
n move the pointer to next line
str1='abcd' str2='efgh' print str1+str2 #output abcdefgh
Using + we can concatenate two strings .
str1='abcd' str2=25 print str1+str2 #output Error - TypeError: __add__ nor __radd__ defined for these operands
Reason being we can’t concatenate string and a number
solution 1 - define number in the format of string so - str2='25'
solution 2 - Convert it into string while printing - str(str2) ,so print str1+str(str2)
to convert from anything to string use str() function .
to replace any string or character with another string or character
str1='123' str2='456' action='dist;item' long_string='ds;sfsdf,sfsdf//' print str1.replace(str1,str2) # output 456 print str1.replace('1','7') # output 723 print action.replace(';',',') # output dist,item #you can also have multiple replace at the same time print long_string.replace(';','').replace(',','').replace('/','') # output dssfsdfsfsdf
Arrays are fun way to handle string and number.To declare a null array use this syntax
str1=['action','item','123'] print str1 # arrays always starts with 0 and so on # output action print str1 #output 123
To declare any as null array to this str1=
Converting Arrays to string
str1=['action','item','123'] print ''.join(map(string.strip,str1)) # using this syntax we can convert the array to string #output actionitem123
Converting String to Array
str1='this is a string' print list(str) #output ['t', 'h', 'i', 's', ' ', 'i', 's', ' ', 'a', ' ', 's', 't', 'r', 'i', 'n', 'g']
Converting characters into Array
str1='this is a string' list1=str1.split(' ') print list1 #output ['this','is','a','string'] print list1 #output - this print list1 #output - is
this split command is useful when you wish to split the string into words and fetch the required on as appropriate.
Raise is a great command to raise an error and we can use raise to publish the required error into the Operator.Example
raise " This is the following Error " , string
Capturing position in String
#first step import module str='12345' # assigning value to String print str[:-1] # print command is used to print in the output ## when we use -1 it means it starts from the end of the string ## output 1234 as end is 5 and so backwards leave 1 character. print str[:-2] ## so leave 2 position from end , so output 123 print str # 0 is treated as the first position print str[0:3] #expected output 123 print str[2:4] #expected output 34
There are some great high level search we can do used regex but here i will be showing you here simple search method using string.find
str1=' This is a program ' print string.find(str1,"is") if string.find(str1,'is')> 0: print str1 else: print ' Not available ' # output - This is a program
whenever string.find , finds the particular string in it refers to the position and that’s the reason I use > 0 .
Another simple and efficient search is using Regular Expression (re) .
import re str1='This is a program' if re.search("is",str1): print ' Match found' else: print ' Not Found'
Please look into link for more way to handle simple and complete expression methods and functions.
os module helps us to do Operating system based activity either in windows or unix.
os.system run an operating system command fetches the error if fails. If the process is successful then 0 else the required error message .
ODI have the OS Command and OdiOSCommand to do pretty much the same work without writing any jython
Lets take another example
import os if os.system (
"copy test.py test1.py") == 0 : print "success" else: print " failure "
For os.system the 0 (zero) is always marked by success and rest of the return codes by failure or error.
Look at this documentation link for os – http://www.jython.org/docs/library/os.html
AND and OR condition
For and condition use this & (and symbol)
OR condition use this | (pipe symbol )
str1= 'Example1' str2='Example2' if (( str1 =='Example1' ) & (str2 == 'Example2')): print ' And works ' #output And works if (( str1 =='Example1' ) | (str2 == 'Example1')): print ' OR works ' #output OR works if ((( str1 =='Example1' ) & (str2 == 'Example3')) | (str2 == 'Example1')): print ' AND and OR combination does not works ' else: print ' AND and OR combination works ' #output - AND and OR combination works
if (condition )
str1='Examples' if str1 == 'Example' : print ' The String is Example ' elif str1 =='example' : print ' The String is example' else: print ' The str1 is neither example (nor) Example '
[ Note – there is always a tab space after if condition ]
While condition is great when we need to process something one by one and execute something till the end.
file_open=open('C:output.txt','r') line =file_open.readline() while line: print line line =file_open.readline()
[ Note – there is always a tab space after while condition , till the condition is complete ]
Jython + SQL
We also use Jython to play with SQL and write the result either in File , Operator etc. To use the SQL we use the Java libraries.
import java.lang.* as lang
import java.sql.* as sql
When it comes to Jython+SQL the logic , format is pretty much the same as Java , so in case you find an example with Java you can modify with few simple syntax change and write the codes for Jython.
The most efficient way is to use the sunopsis API getJDBCConnection for connecting to Source system using Java or Jython. This way we don’t have to worry about providing the driver parameters or hard coding them since they can change from the environment to environment. In case if you ever need to get particular info about driver , url or user you can use API getInfo()
Initially create the connection to the Source System
sourceConnection = odiRef.getJDBCConnection(
Here in Command on source we select the required technology and associated schema.
sqlstring = sourceConnection.createStatement()
Next step is to create the statement for execution .
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)"
In sqlstmt we define the complete select , insert, update string and pass it to this variable. This way we can call it multiple time and if we have to modify , we can change it at one place.
Here we have used t to have the tab space between the column and the output and n to have the next result in second line this way the result can be more organized. For an output example Please visit this link –http://odiexperts.com/error-records-log
To execute a select query use executeQuery(sqlstmt) and to insert /update query use executeUpdate(sqlstmt) .
In order for you to read and process the select query we tend read it records using a while loop and finally you can use getInt , getString and fetch the required records . Please look into the Java documentation for other APIs.
Also you can other similar example related to Jython + SQL
Looking for Editor to learn and practise Jython.
1. Eclipse [ Plugins such as Pydev , JyDT ]
Jython Sources and Documentation
If you are looking for Documentation on Jython , the best resource would be to look into official documentation of jython
http://www.jython.org/docs/library/ [ Very Important Link ]
[ Note : The links are for Jython 2.5 and so you might find functions and methods which might not be still supported as ODI 10g is on 2.1 ]
Also you might not find so many codes or example for Jython , but you can refer to Python documentation and example too , as Jython is Java + Python .
You can find Python documentation here http://python.org/doc/
also look into the Oracle documentation ( http://download.oracle.com/docs/cd/E15985_01/doc.10136/reference/DIJQR.pdf ) there are some good example covered .
Jython not recognizing Windows 7 as NT
Please look into this link http://odiexperts.com/jython-os-bug-in-windows-7
Handling spaces in path
While passing the OS system path especially in windows we have issue with spaces causing the error Path does not exists or File not Found etc . In order to solve that , Please have the spaces between folder name between double parenthesis. Say for example
import os ospath=(r'C:Program" "FilesMicrosoft" "SQL" "Server\100\Tools\Binn\bcp') if os.system(ospath) > 0: raise 'BCP Unload Failes'
Program File is – Program” “File ( Program<double_quote><space><double_quote>File).
Microsoft SQL Server is Microsoft” “SQL” “Server
\ in windows path
Its good to use the two slashes in windows path , reason being some time some of the combination yields values which are understood by Jython differently , say for example
In this example the n and t would be understood by Jython as next line and tab space causing to read the line wrong and would throw errors like file not found or path does not exists etc ,so the correct way to write is (ie using double slash)
Hope this should provide beginners a guide towards the world of Jython+ODI .
We have covered few codes of Jython and might update this post in future too so that we have consolidated Jython guide at one place.
[ Note: There are numerous other ways to do the above codes in different way , you can explore more by looking into Jython books and documentation. ]
Keep looking into the odiexperts for more tips and tricks .