Send e-mail using Jython in Oracle Data Integrator

Hello friends!

Oracle Data Integrator includes a built-in tool called OdiSendMail which is used to send an email through a SMTP server. However, OdiSendMail will work only if the mail server does not require authentication.

In this post, I will demonstrate how to send an e-mail with the help of Gmail’s SMTP server. Instead of going for the OdiSendMail tool, I will be using the Jython technology in order to implement the same functionality.

Pre-requisites: Oracle Data Integrator 11g (build version 11.1.1.7.0)

Open ODI Studio and follow the below steps!

Step 1: Create a new Procedure

Create a new Procedure under any of your existing Projects in the Designer tab. Name the procedure SendEmail. Switch to Details and add a new entry.

create new procedure

procedure_1

Now, under Command on Target, you need to write the Jython code. Please make sure you have selected Technology as Jython.

procedure_3

Jython code

import smtplib
import string
BODY = string.join((
"From: %s" % 'senderemailaddress@gmail.com',
"To: %s" % 'recipientemailaddress@gmail.com',
"Subject: %s" % 'Test Mail',
"",'This is a test email from ODI'
), "\r\n")
sender = smtplib.SMTP('smtp.gmail.com',587)
sender.set_debuglevel(1)
sender.ehlo()
sender.starttls()
sender.ehlo()
sender.login('senderusername', 'senderpassword')
sender.sendmail('senderemailaddress',['recipientemailaddress'],BODY)
sender.close()

Note: You need to enter your valid Gmail credentials and replace sender and recipient address with valid email addresses in the above code.

Finally, run this procedure. Check the status of the operation under the Operator tab.

Output

Output

output_2

Source: Send mail in ODI using Gmail Credentials

Tutorial #33: Using Custom Java code in ODI

Hello everyone!

It’s time for another cool tutorial on Oracle Data Integrator. This one will guide you in using your custom Java code and help call .jar files from within ODI.

Pre-requisites: Oracle Data Integrator 11g (build version 11.1.1.0.7)

Step 1: Write Java code to perform Addition of two numbers

Open notepad and start coding. Here is what my Java class looks like:

Addition.java


import java.util.Scanner;

public class Addition 
{
	
	public int number1, number2;
	

	public int add(int num1, int num2)
	{
		return (num1+ num2);
	}
	
	public static void main(String args[]) throws Exception
	{
		
		int num1,num2;
		
		Addition addobject = new Addition();
		System.out.println("--------------Program to add two numbers---------------");
		Scanner s = new Scanner(System.in);
		System.out.println("Enter 1st number");
		num1= s.nextInt();
		System.out.println("Enter 2nd number");
		num2=s.nextInt();
		
		System.out.println("result of addition is :" + " " +addobject.add(num1, num2) );	
	}

}

Step 2: Compile and create .jar file

Compile the .java class from the command line

C:\programs\javac Addition.java

Create .jar file from the class

C:\programs\jar cf Addition.jar Addition.class

Copy the newly created Addition.jar file and paste it at the following locations!

1st location

C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\agent\drivers

2nd location

C:\Users\<user-name>\AppData\Roaming\odi\oracledi\userlib

At the second location, you will find the additional_path.txt file. Open it and add the path to your .jar file.

; Additional paths file
; You can add here paths to additional libraries
; Examples:
;	C:\ java\libs\myjar.jar
;	C:\ java\libs\myzip.zip
;            C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\agent\drivers\
;	C:\java\libs\*.jar will add all jars contained in the C:\java\libs\ directory
;	C:\java\libs\**\*.jar will add all jars contained in the C:\java\libs\ directory and subdirectories

C:\Users\karan\AppData\Roaming\odi\oracledi\userlib\*.jar

Workaround

After you have copied the .jar file you now need to do a workaround. Copy the Lib folder from C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi.sdk\lib and paste it inside C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\agent\lib. This makes sure that ODI reads the python files when we try to import them in our procedure code.

Step 3: Create new procedure

Launch ODI Studio and connect to your work repository. Create new procedure named AddTwoNumbers in any of your existing Projects.

Create new procedure

create_procedure_2

Now, write the command for the procedure in Jython

import os
import sys

jars = [
"C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\agent\drivers\Addition.jar"
]

for jar in jars:
      sys.path.append(jar)

import Addition

num1 = <%=odiRef.getOption("number1")%>
num2 = <%=odiRef.getOption("number2")%>

print  "1st number: %d" % num1
print  "2nd number: %d" % num2

addobj= Addition()

result= addobj.add(num1,num2)

print "Sum is : %d" % result

Note: Make sure the above code is well indented or else it will result in errors when executed.

Before you run this procedure you need to create two Options that will act as input parameters for the procedure.

option_1

option_2

Step 4: Create new package

Create new package named Addition of Two Numbers. Add the procedure to the package with the correct options and run the package. Check the status of the operation under the Operator navigator.

Create new package

create_package_2

create_package_3

Output

Output

Note: In order to view the output on the command line window, one can Create a new Agent and run the above package by selecting your agent.

Tutorial #26: Using Jython in ODI

Hello everyone!

Since the past few weeks, I have been learning a bit of Jython in order to implement a certain functionality in Oracle Data Integrator. If you happen to read the developer’s guide for Oracle Data Integrator you will come to know that Jython is mainly used to capture or raise errors, handle RDBMS operations and run OS specific commands.

In you are not aware of Jython, I suggest you read about it before you begin with this tutorial from over here. To start learning about the use of Jython in ODI, I would recommend reading the book Jython’s beginner guide for ODI.

Once you are familiar with Jython, you can now start using it in ODI. Open ODI Studio and carry out the following steps!

Step 1: Create data server and physical schema for Jython technology

Under Topology navigator, select the Physical architecture tab. Right click Jython–> New data server

Create new data server

Create new data server

Now, create a new physical schema for the same

create physical schema

create physical schema

Step 2: Create new logical schema for Jython technology

Under Topology navigator, select the Logical architecture tab. Right click Jython–> New logical schema

create logical schema

Step 3: Create a new Procedure

In any of your existing Projects under Designer tab, create a new procedure as follows:

Create new procedure

You now have to write your Jython code using proper indentations under Command on target. Specify the directory where you want the output file to be created. Over here I have specified c:/count_write.txt

Jython code

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'"

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)+'\n\n'+str(count)

print >> output_write ,rs


procedure_2

Under Command on Source, just select any logical schema present for Oracle technology and do not write any code for it.

procedure_3

In case you need to write more queries, you can find them on this blog post by ODI experts.

Save and run the procedure. Make sure no errors are present. For the output, check the directory that you have mentioned in your code. You should now see a new text file created containing your result!

Output

output_2

Time for some Jython magic!

Hello,

I love trying out new programming languages and my next learning would be Jython, an implementation of the Python programming language written in Java. Through this post, I will walk through the basic installation of Jython in Windows and help you write a small Jython program! 🙂

Follow these simple steps and you can start programming using Jython in no time!

Note: Ensure that you have the latest version of Java installed on your machine before you proceed further.

Step 1: Download the latest version of Jython from here. You need to download the .jar file for Jython-installer.

Step 2: After you have downloaded Jython, open Command prompt, navigate to the location where your downloaded .jar file exists and type:

java -jar jython-installer-2.7.jar

This should launch setup window for Jython. Just follow the instructions and you should have Jython configured in no time!

Step 3: Once installation is done you can now navigate to the bin folder inside Jython and double click the jython batch file. You will then see a window as follows:

jython_1

Now let’s write a simple program to import Java swing library! I will be using the Python IDLE to write the code.

swing_demo.py

from pawt import swing
import java

def exit(e): java.lang.System.exit(0)

frame = swing.JFrame('Swing Demo using Jython', visible=1)
button = swing.JButton('Click to close!', actionPerformed=exit)
frame.contentPane.add(button)
frame.pack()

If everything works fine and no errors are displayed you should get a new window similar to the one using Java Swing API. Happy coding! 🙂