Run scenario through Oracle Data Integrator console

Hi guys!

In the previous tutorial, I discussed about installing the ODI console application using Weblogic server. In this post, I will talk about running an existing scenario through the ODI console.

Pre-requisites: Oracle Data Integrator 11gR1 (build version

Step 1: Create a scenario

Launch the ODI Studio application. Login to your work repository and create a scenario of any of your existing Interfaces or Packages.


Step 2: Run ODI console

Start the Admin Server in running mode.


Open the web browser and type, http://localhost:7001/console

Login using your weblogic credentials and run the ODI console application.


Login to your work repository. Go to Runtime –> Scenarios and you should see the list of scenarios.

Step 3: Run local agent

Before running the scenario, you need to create and start the local agent. Refer this post for more details.

Once the agent is running successfully, execute the scenario. Verify the status of the operation from within the Operator tab in studio.



Tutorial #53: Installing ODIConsole application using weblogic server

Hello everyone!

Here is an interesting tutorial that explains how to install ODI (Oracle Data Integrator) Console application using WebLogic server. The ODI Console is basically a web based console using ADF-faces framework for monitoring and managing the runtime architecture as well as viewing the design time objects.

Using ODI console, developers can view the interfaces whereas administrators can create and import repositories in order to configure the Topology. Through this post, we will learn how to setup the console application from scratch in Oracle Data Integrator!

Pre-requisites: Oracle Data Integrator 11gR1 (build version, Weblogic server 11gR1 10.3.5, Windows XP SP3 32 bit OS machine

Step 1: Download and install Weblogic server 11gR1 10.3.5

Go to the downloads page for Oracle Weblogic server and install a proper bit version of the same. Once downloaded, run the .exe file. After the installation is completed, you should see a Middleware folder being created in the drive specified. For example, mine exists at D:\Middleware


Step 2: Download and install Oracle Data Integrator

In order to install the console application, you first need to install ODI inside the created Middleware directory, D:\Middleware, with the necessary components checked. Please check this blogpost. During ODI installation make sure you have chosen to install the ODI console component. The installation procedure is almost the same in all the versions till date. Create a master and a work repository before proceeding further.

Step 3: Configuring the domain

Using command prompt navigate upto the bin directory (D:\Middleware\Oracle_ODI1\common\bin) and execute the config.bat file! A configuration wizard should open up.

Now follow the below screenshots!



Make sure you select the option Oracle Data Integrator – Console










Add the Admin Server by clicking the arrow button.





Click on the Create button and the domain creation would begin. Start the Admin Server when completed.

Once the server is up and in running mode, open a new tab in your web browser.


Enter the address:


Change the local host and port to whatever is listed by you in the Admin Server URL above.

Login into weblogic server with the credentials you provided during installation. On the left hand side, click on Environment –> Servers and then select the Admin Server (which should be running)






Enter SUPERVISOR credentials for logging into the ODI console!



Finally, this completes the installation of ODI console. That’s it for this tutorial. Hope it helps! πŸ™‚

Tutorial #52: Create and deploy data services in ODI

Hi everyone!

One of the most striking features in ODI is it’s ability to fully integrate into a SOA (Service Oriented Architecture) environment. Data services expose the enterprise data and make it available to users in the form of a web service. Using ODI, you can both create data services and then consume them via web services.

In this tutorial, I will explain to how to create and deploy a data service in Oracle Data Integrator.

Pre-requisites: Oracle Data Integrator 11g (build version, Oracle Weblogic server 11gR1 (10.3.6) with OEPE package installer, Windows XP Service Pack 3 (32 bit OS)

Step 1: Create Physical and Logical schema for Oracle table

Create a sample table in your Oracle database. In addition, you should also have a data server, physical and logical schema already existing for the Oracle connection. You can re-use the same over here. If you haven’t created the schemas, then you can do so from within the Topology navigator.

For example, I created the following table. Insert a few dummy records in the table.

create table KSTUDENT(studentid int primary key, studentname varchar(20));

Also, reverse engineer the model by creating a new Model folder.

Step 2: Extend existing weblogic domain

Go to C:\oracle\Middleware\Oracle_ODI1\common\bin and run the config.bat file. A new configuration wizard should open. Choose the option to extend your domain. Make sure you have selected the SDK web services this time. Proceed with the installation. At the end you should see an empty autodeploy folder in the domain directory, say for example,


This is the directory where ODI deploys auto generated code for the data services.


Step 3: Create new weblogic container in ODI

Under Technologies in the topology navigator, select JAX-WS and create a new data server, physical and logical schema as follows!




Step 4: Create new JDBC data source for schema

Start the weblogic server and make sure it is in Running mode. Log on to the Weblogic console (the default URL for the Weblogic console is http://localhost:7001/console) and navigate to Services –> Data Sources.


Create a new Generic Data Source and name it JDBC KE. AS JNDI name we mention jdbc/KE.


Now click Next and follow the below steps!






Step 5: Generate, deploy and test data services using SKM knowledge module

Import the SKM Oracle knowledge module in any your existing Projects. Open the source model under the Models folder in the Designer navigator. Go to Services and add the following!



Finally, click the Generate and deploy option and your data service will be successfully deployed in the autodeploy folder!


Step 6: Testing the data service

We log into our Weblogic console, example http://localhost:7001/console , go to Deployments and expand _appsdir_STUDENT_TABLE_DATA_war (autodeployed).



Then we click on WSKstudent and then go to the Testing tab. We can view the WSDL by clicking the ?WSDL link.


Hence we have successfully created, deployed and tested our data service using Oracle Data Integrator! Hope it helps! πŸ™‚

Tutorial #51: Install Oracle GoldenGate Director Server and Client

Hey everyone!

Apart from the standard GoldenGate Software Command Line Interface (GGSCI) tool, Oracle provides a Director client and a Director server under the management pack for GoldenGate.

In this tutorial, we will learn how to install and configure Oracle GoldenGate director client and server!

Pre-requisites: Windows XP Service Pack 2 (32 bit), JDK 1.6.0_20 or later, Microsoft Visual C++ 2005 SP1 Redistributable, Oracle weblogic server 10.3.3 or later

Step 1: Download & install Director server and client for GoldenGate

Download the latest version of the GoldenGate Director server and client depending upon your system requirements from over here. Make sure you download and install the GoldenGate Monitor tool as well.


Install all three components namely client, server and monitor successfully on your system. While installing the director server if you get an error like unable to run-cdc-tool script file, then examine the contents of the log file. Check the JDBC connection details and once again try installing.

Step 2: Start the Director server

Go to Start –> Programs –> Oracle GoldenGate Director Server and start the director server. A new command prompt instance should automatically open and the server should be in running mode!


Step 3: Using Director client to configure data source

Open the Director client Administrator tool. By default the username and password are both admin. While installing the director server I had specified the port as 7004. My login screen looks like this:


Once logged in, you can change the admin account password or keep it as it is. You can also add/configure a new data source and test it’s connection as follows!


Switch to the Monitor Agent tab and click on the Start Selected Monitor button.


Finally, launch the Oracle GoldenGate Director Client tool. Run the ggsci command info manager. You should see the manager running on the specified port! πŸ™‚


This completes the tutorial. We have successfully installed and configured GoldenGate Director Client and Server. The Director Web console can also be seen by entering http://localhost:7004/acon in your browser. It should show you the following page!


Source: Oracle GoldenGate Director (Server,Client,Web) installation

Tutorial #50: Using load plans in Oracle Data Integrator

Hello everyone!

Load plans were introduced in Oracle Data Integrator from version Load Plans are nothing but objects for organizing and launching scenarios in a production context.

An ODI Load Plan is made up of a sequence of several types of steps. Each step can contain several small steps. Depending on the step type, the steps can be executed conditionally, in parallel, or sequentially. By default, an ODI Load Plan contains an empty root serial step. This root step is mandatory and the step type cannot be changed.

In this tutorial I will explain how to create a simple load plan in Oracle Data Integrator.

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

Step 1: Create a scenario

In this case I would be creating a scenario of one of my existing interfaces. You can refer this tutorial for creating a file to table load interface. Once you have created the interface, generate a scenario for the same.

Step 2: Create a load plan

Create a new load plan named TestLoadPlan1 by selecting Load Plans–> New Load Plan under the Designer navigator as follows!



Add a new Serial step named Serial. Then once again add a Serial step specifying the name of the task to be performed. Finally add a Scenario to this step for execution.

Validate the load plan if required and then execute it! πŸ™‚

Note: In order to execute a load plan, you need to create a new stand alone agent in ODI. Refer this tutorial for more details. Make sure your agent is selected and running before you execute your load plan.


Once you execute the plan, you should get a message saying “Load Plan started”. Check the status of the operation under the Operator tab. If the operation is successful, you should see the data in the target table! πŸ™‚



Tutorial #49: Creating Java EE Agent in Oracle Data Integrator

Hello friends!

Today we will learn how to create a Java EE agent in Oracle Data Integrator. In one of my previous tutorials I had explained how to create a stand-alone agent in ODI. The only difference in this case is that we would require to install Weblogic server in order to configure our Java EE agent.

Pre-requisites: Oracle Data Integrator 11gR1 (build version, Oracle WebLogic Server 10.3.6 (x86 for 32 bit JVM)

Step 1: Download Oracle weblogic server

Go to the Oracle software downloads page and download a previous release 10.3.6 of Oracle Weblogic server.


Note: I assume while installing Oracle Data Integrator, you have selected the option to install Java EE agent as well. If not, I would recommend carrying out a fresh installation before proceeding with this tutorial.

Step 2: Deploy and configure new domain with Weblogic server

Open command prompt and change directory to \common\bin, for example: C:\oracle\Middleware\Oracle_ODI2\common\bin. Execute config command.

Follow the screenshots below and enter the information required for various fields as shown in the screenshots!














Click on the Create button and let the installation take place!

Note: Don’t start Admin Server now

Step 3: Create ODI Java EE agent for deployment on domain ODI-DOMAIN







Step 4: Connect to Weblogic server and managed server odi_server1

Change directory to the directory of the WLS Home –> C:\Oracle\Middleware\user_projects\domains\ODI_DOMAIN\bin. Execute the startweblogic command.



Security must be set up for the JAVA EE application to have access to the ODI repository. The entry will be created within the credential store, which will allow the JAVA EE Agent to authenticate itself to be able to use the required resources. This user should be a user that is already set up in the ODI Security.

Change directory to the WLS_HOME common bin –> C:\Oracle\Middleware\oracle_common\common\bin, and then execute the command wlst.



To connect to the running Admin server, execute the following command: connect(‘weblogic’,’welcome1′,’t3://localhost:7001′)

Note: Here welcome1 is my weblogic password.



Execute the following command to add the correct credential store for ODI Supervisor : createCred (map=”oracle.odi.credmap”, key=”SUPERVISOR”, user=”SUPERVISOR”, password=”SUNOPSIS”, desc=”ODI SUPERVISOR Credential”).



Note: All commands are case-sensitive! You can copy commands from the text file provided here.

Also execute the command to add the correct credential store for the domain ODI-DOMAIN: createCred (map=”oracle.odi.credmap”, key=”ODI-DOMAIN”, user=”weblogic”, password=”welcome1″, desc=”ODI-DOMAIN Credential”). To exit WLST, execute the command exit() .



To start managed server odi_server1, change directory to WLS Home –> C:\Oracle\Middleware\user_projects\domains\ODI-DOMAIN\bin , and use the execute command.

StartManagedWeblogic odi_server1. For Username, enter weblogic. For the password, enter welcome1. Verify that your managed server is started in RUNNING mode.



Finally, when the weblogic server is up and running, test the connection to the ODI agent created previously. A successful connection indicates that you have configured the Java EE agent correctly. That’s it for this tutorial. Have a nice day! πŸ™‚


Source: Deploying and configuring Java EE Agent

Tutorial #48: Execute stored procedure using SQLEXEC in Oracle GoldenGate

Hi everyone!

It’s time for another interesting GoldenGate tutorial. In GoldenGate, the SQLEXEC parameter can be used with Extract or Replicat processes to connect to the database via stored procedures, SQL queries and DML operations.

In this tutorial, I will explain how to use SQLEXEC to execute a stored procedure in Oracle GoldenGate!

Pre-requisites: Oracle GoldenGate 11g (for 64 bit Windows), Oracle database 11gR2 Enterprise Edition

Step 1: Create tables in Oracle

Create the following tables in your Oracle database. I have created these tables under SYSTEM schema!

create table creditcard_accounts(account_code int primary key, account_id int, account_name varchar(10));

create table new_account(new_account_id int primary key, new_account_name varchar(10));

At present, do not insert rows in any of these tables. We will perform insertion during the replication process!

Step 2: Create stored procedure

Now, create a simple stored procedure as follows. Make sure the schema in which you have created this procedure (in my case SYSTEM) has execute and select privileges on the stored procedure. In some cases, you might even consider granting DBA rights to the schema.

create or replace procedure lookup_account(acc_code in int,acc_name out varchar)
select account_name into acc_name from creditcard_accounts where account_code=acc_code;

Step 3: Create Extract and Replicat processes

Open the GGSCI interface. Create an Extract and a Replicat process containing the SQLEXEC parameter as follows!

Create Extract

extract extl
userid ggs_owner, password ggs_owner
rmthost , mgrport 7809
rmttrail ./dirdat/kk
table system.creditcard_accounts;

Create Replicat

USERID ggs_owner, PASSWORD ggs_owner
SOURCEDEFS ./dirsql/tables_credit.sql 
MAP system.creditcard_accounts, TARGET system.new_account,& 
PARAMS (acc_code = ACCOUNT_CODE)), & 

Step 4: Create definitions file

Since both the table structures are different, I am going to create a definitions file that can be used by the Replicat when specified using the SOURCEDEFS parameter.

Create definitions file

# On the SOURCE
ggsci> edit params defgen
defsfile ./dirsql/tables_credit.sql
userid ggs_owner password ggs_owner

Using the DEFGEN utility generate the definition file.

C:\gg_src\defgen paramfile ./dirprm/defgen.prm

Step 5: Start Extract, Replicat and Manager process

Start all the processes. Check whether all the processes are running using the info command. Now, try inserting a new record in the creditcard_accounts table. If all processes are running successfully, the stored procedure gets executed and populates the the new_account table. You can verify the same by performing a select operation!





GG nightout #1: Send SMS automatically using Oracle GoldenGate

Hi, folks!

My name is Daniel Reymour. Since the past three years, I have been working on Oracle technologies/products including Oracle Fusion Middleware, Oracle Secure Enterprise Search and OEM. I am one of those developers trapped inside a sysadmin’s body, a FOSS evangelist, an avid reader and a professional guitarist! πŸ™‚

Sending an SMS automatically is pretty similar to Sending An E-mail. We only need to modify the script contained within the batch file!

However, in order to generate the batch file we would be using Selenium, a popular application used for automating web applications for testing purposes. With Selenium configured, all we need is an online SMS sending portal to send our messages.

Our replicat file, just as mentioned in the email tutorial would be as follows; the only change includes adding the sendsms.bat file.

Create Replicat

GGSCI (jatin-PC) 30> add replicat replocal exttrail ./dirdat/aa
GGSCI (jatin-PC) 31> edit params replocal
--add following lines in the notepad file
REPLICAT replocal
USERID ggs_owner, PASSWORD ggs_owner
MAP system.same_host_src, TARGET ggs_owner.same_host_src, FILTER (@STREQ (EMPCITY, "pune" )), EVENTACTIONS ( IGNORE TRANS , SHELL "C:\programs\sendsms.bat");

To test this, let’s create a Java Project named GoldenGateSMS with package name com.example. Create a new class called

//all necessary imports go over here

public class TestSMS
    private Selenium selenium;

    public void setUp() throws Exception
	WebDriver driver = new FirefoxDriver();
	String baseUrl = "";
	selenium = new WebDriverBackedSelenium(driver, baseUrl);

    public void testSMS() throws Exception
	selenium.type("id=password", yourpassword);
	selenium.type("id=username", yourusername);"name=Login");
	selenium.type("css=#textArea", "Test Message from Oracle GoldenGate!");
	selenium.type("css=#textfield2 + input", yournumber);

   public void tearDown() throws Exception 


Export the java project as a .jar and call it within the batch file. For testing purposes, you can run the .jar file via command prompt as shown below. When the file runs, Selenium takes over and at the end you should receive an SMS on the number that you had mentioned.



Voila! We are done over here. BTW, this isn’t my personal space and I would like to thank Karan, friend and fellow GAD for giving me this opportunity! πŸ™‚

Determine ports on which Extract and Replicat process run in GoldenGate


Today a friend of mine asked me how to determine on which port the Extract and the Replicat process run in Oracle GoldenGate. I was quick to respond with the nmap (network mapper) tool since I have been using it on my Linux machine for a long time now!

However, since most aren’t quite familiar with the tool, I decided to go with a Windows command called netstat. This command displays active TCP connections, ports on which the computer is listening, Ethernet statistics, IP routing table, IPv4 statistics (for the IP, ICMP, TCP, and UDP protocols), and IPv6 statistics (for the IPv6, ICMPv6, TCP over IPv6, and UDP over IPv6 protocols)

Let’s first start our GoldenGate Software Command Line Interface (GGSCI) by writing the following command!

Navigate to GoldenGate home (C:\gg_src) and type:


Now start the Manager process and obtain it’s information using the info command!

start manager
info manager


So this confirms that the Manager process is running on port number 7809.

Now, start any of your Extracts or Replicats as follows!


While all these processes are running, open an instance of the Windows Command Prompt (in Administrator mode) and type the following netstat command.

netstat -abno


-a Displays all connections and listening ports.

-b Displays the executable involved in creating each connection or listening port. In some cases well-known executables host multiple independent components, and in these cases the sequence of components involved in creating the connection or listening port is displayed. In this case the executable name is in [] at the bottom, on top is the component it called, and so forth until TCP/IP was reached. Note that this option can be time-consuming and will fail unless you have sufficient permissions.

-n Displays addresses and port numbers in numerical form.

-o Displays the owning process ID associated with each connection.


Once you run the netstat command search for the extract.exe and the replicat.exe files. Note down the ports on which they are running. The Extract will correspond to the port mentioned for extract.exe and the Replicat will correspond to the port mentioned for replicat.exe.

As seen above the Extract is running on port 7840 and Replicat is running on port 7841.

Display Rating Bar in Android

Hello everyone!

Android provides developers a Rating Bar widget to display rating bar components in the form of star icons. As mentioned in the Android developer’s guide, the RatingBar is an extension of SeekBar and ProgressBar that shows a rating in stars. The user can touch/drag or use arrow keys to set the rating when using the default size RatingBar.

In this tutorial, I will explain how to display a simple RatingBar in Android. When the user clicks on the rating bar’s star, the selected rating value will be displayed in a TextView.

Pre-requisites: Eclipse IDE, Android SDK (tested below code on Android 2.3.3)

Open up the Eclipse IDE. Create a new Android application project named AndroidRatingBarExample. Let the package name be com.example and Activity name be MainActivity. Write the following code in your Activity class!

package com.example;

import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.RatingBar;
import android.widget.RatingBar.OnRatingBarChangeListener;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity 
	private RatingBar ratingBar;
	private TextView txtRatingValue;
	private Button btnSubmit;
	public void onCreate(Bundle savedInstanceState) 

	ratingBar = (RatingBar) findViewById(;
	txtRatingValue = (TextView) findViewById(;
	btnSubmit = (Button) findViewById(;

	//display the current rating value in textview
	ratingBar.setOnRatingBarChangeListener(new OnRatingBarChangeListener() 
		public void onRatingChanged(RatingBar ratingBar, float rating, boolean fromUser) 

	//on click button display the current rating value.
	btnSubmit.setOnClickListener(new OnClickListener() 
		public void onClick(View v) 


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android=""
        android:text="Give my app a rating"

No changes are to be made to the AndroidManifest.xml file. Save all changes and make sure no errors are present. Run the project on an emulator or an actual device. You should see the following output!


Source code for this tutorial can be found over here