Tutorial #16: Creating master and work repositories in ODI 11g

Hello friends,

In my last tutorial I talked about the installation of Oracle Data Integrator 11gR1 on a Windows machine. In this post I will explain how to go about creating and then connecting to master and work repositories!

Pre-requisites: Oracle 10g Express Edition with *SQLPlus, Oracle Data Integrator (ODI) version 11gR1.

First, open Command Prompt and type sqlplus. Connect to your system schema by providing the valid username and password.

Now, you need to create an RDBMS schema for your master repository. For that execute the following SQL commands.

SQL> create user ODIMR identified by password default tablespace users temporary tablespace temp;
SQL> grant connect, resource to ODIMR;

1. Creating a Master Repository

Step 1: Open ODI Studio. Open the New Gallery by choosing File –> New. In the New Gallery, in the Categories tree, select ODI and then click on Master Repository Creation wizard.

Step 1 for Master repository creation

Step 1 for creating master repository

Step 2: In the wizard, select Oracle JDBC Driver. Add the JDBC URL as jdbc:oracle:thin:localhost:1521:orcl. Enter the User as ODIMR and the Password as password. Click the Test Connection button and verify successful connection. DBA User and Password will be the same you entered while logging in to *SQLPLUS. Click Next to proceed.

Step 2 for Creating master repository

Step 2 for creating master repository

Step 3: In the Authentication window, enter Supervisor Password as password. Click Next. In the Password storage window, select Internal password storage and click Finish.

Step 3 for connecting master repository

Step 3 for creating master repository

2. Connect to master repository

Step 1: Open the New Gallery by choosing File –> New. In the New Gallery, in the Categories tree, select ODI. Select Create a New ODI Repository login.

connect to master repository

connect to master repository

Step 2: Enter the JDBC URL and other information fields and select jdbc:oracle:thin:@”host”:”port”:”sid” in order to enter the proper URL.

connect to master repository

Step 3: Finally, click on Connect to Repository on the main screen in ODI. Enter the credentials with login name ODIMR.

connect to master repository

connect to master repository

3. Create Work repository

Step 1: Create a schema for the work repository by executing the below SQL commands. I have given name as ODIWR for my schema.

SQL> create user ODIWR identified by password default tablespace users temporary tablespace temp;
SQL> grant connect, resource to ODIWR;

Step 2: Go to Topology Navigator tab. Select Repositories. Under that right click Work Repository and select New Work Repository. This will begin the process of creating a new work repository.

create work repository

create work repository

Step 3: In the following screen enter the required parameters once again. Enter User as ODIWR and Password as password. Make sure you test the connection before proceeding further.

Step 4: In the Specify Work Repository properties set the ID to “121”. Set the Name to WORKREP121. Enter Password as password. For Work Repository Type, leave Development. Click Finish.

create work repository

create work repository

Step 5: When asked for login name, enter name as ODIWR23. View the newly created repository under the work repositories tree view.

create work repository

create work repository

Finally, disconnect from the master repository and try connecting to work repository now created. Select user as ODIWR23 and Password as password.

connect to work repository

connect to work repository

connect to work repository

Tutorial #15: Installing Oracle Data Integrator 11gR1


Oracle used to sell OWB (Oracle Warehouse Builder) before. They then bought ODI (Oracle Data Integrator) from Sunopsis. Although ODI is not a replacement for OWB, it is surely expected to be used by many Oracle Applications in the future. It is now a part of the Oracle Fusion Middleware.

What is ODI? What are it’s features?

Oracle Data Integrator is a platform that includes data integration requirements, high performance batch loads and supports SOA enabled data services. It is basically an ELT tool used for data integration projects, data warehousing and so on.

Features of ODI are as follows:

1) ODI supports heterogeneous databases, not only Oracle.
2) Supports batch, event based and real-time integration.
3) Provides extensibility through use of Knowledge Modules.
4) It is built for SOA environments and supports web services.

ODI 11gR1 Installation

In order to start using ODI, you need to first download it’s latest version from the Oracle product downloads page.

In this tutorial, I will be installing ODI on a Windows XP machine. So make sure you download the appropriate installer depending upon your operating system.

Once you have downloaded the installer, run the setup.exe file to begin installation!

ODI Installer Folder

On double clicking the .exe file you should get a screen as follows:


Then click on Next and proceed to step 2. Select the components you wish to install. By default the Oracle Studio will be installed.



Proceed to steps 3 and 4 of the installation process. In step 5, you have two choices. Either you can Configure Existing Repositories or you may Skip Repository Configuration and create the repositories manually later. I am going with the 2nd option in this case. I will demonstrate how to create the repositories in my next tutorial.



Click on Next and the installation process will follow. After installation, you can now open ODI Studio through Start –> Programs –> Oracle –> Oracle Data Integrator –> Oracle Studio.

If your ODI Studio opens up, then your installation was successfully completed! You can now proceed with creating repositories! 🙂

Happy Birthday Benson!

“I walk a lonely road, the only one that I have ever known”

Today I may call myself an Engineer. Honestly, I still ain’t one. In those four long years of engineering there were moments where I felt why am I doing this, there were moments when the fear of failure overcomed the will to succeed, moments where I just needed someone to support me. In such testing times, there are only a few who would dare to walk beside you. Benson was certainly one of them.

“Sometimes I wish someone out there will find me”

Even today everytime I board the CST bound train from Vashi, my eyes begin searching for someone, someone with whom I could share even the smallest of secrets, argue, fight , talk on any random topic and all I would get in return is a beautiful smile. After a rough day at college, we both would leave together. At Vashi station, I would never forget to munch a Vada pav from Suniel Shetty’s hotel (pun intended) while Benson preferred a soft drink. We would then head towards platform no: 4 to board our then favorite 4:48 wadala local. Every moment of that 25 minute journey was memorable. We would talk about the teachers in college, linkups in class, assignments, tests, football, movies and anything that would help keep the conversation going. We would create a competitive atmosphere everytime we played Robo soccer on my mobile. Even as Tilak Nagar approached, our talks would never end as we continued from where we left; the next day.

“Check my vital signs. To know I’m still alive. I walk alone”

Today, I might have changed but I am sure he wouldn’t have. In life there are people whom you really can’t forget. I wish someday we both could embark on that same journey from Vashi, hoping this one never ends….

Happy Birthday Benson! Have a rocking year ahead! 🙂

Happy Birthday Benson!

Every drop counts!

Last week was truly a memorable one. I went for my very first blood donation camp held on 7th March in our office premises. I was initially nervous and thought I would feel dizzy after donating blood. However, everything went smoothly and it took around 20 minutes.

Soon after donation, I rushed to the canteen to have a glass of buttermilk. I was feeling very hungry which is normal I guess after one donates blood. As a token of appreciation, I received a certificate for the same from the officials who had organized the camp. I felt that the camp was in a way successful in creating a “feel good factor” among employees like me who look forward to participate in many such blood donation camps in the future!

On the whole it was a truly memorable experience. No wonder people say, that the act of donating blood is a purely altruistic act. As people who donate we just need to remember that every drop counts! 🙂

Getting started with COBOL!

Hello everyone,

Today’s post is something new, different from all my previous posts and talks about one of the oldest programming languages out there called COBOL! COmmon Business Oriented Language (COBOL) was designed by Grace Hopper and first developed by the CODASYL Committee back in 1960. It was mainly designed to develop typical business, file-oriented applications.

Through this post, I will demonstrate one of the ways to setup COBOL on your machine and get started with writing some COBOL programs!


1. Operating System: Windows XP only

2. Microsoft Visual Studio 2008

3. Microfocus Net Express 5.1

So then lets begin!

First of all you need to download and setup Microsoft Visual Studio 2008 from over here. This will ensure you get .NET Framework 3.5 which is needed for using the Microfocus tool.

Now, Microfocus NET Expresss 5.1 is a COBOL development environment and extends support for the .NET platform. It can be used for building COBOL enterprise components and business applications!

You can download the tool from over here

Installing Microfocus NET Express 5.1

Once you have downloaded the tool, double click the Application file to begin setup!

Installation window

Installation window

If you have the serial key, you can go ahead and enter it to obtain the full version!

Customer information

Customer information

Once both Visual Studio 2008 and Microfocus NET Express 5.1 are installed properly,you can now proceed to writing your first COBOL program!

Writing your first COBOL program

Step 1 : Launch NET Express ( Start –> Programs –> NET Express)


Step 2: Go to New –> Program

Now, in the editor window type your COBOL code


Step 3: Compile and save the program. This should create a .int file for you automatically. It will be present in the same directory where you saved your program.

Step 4: Now, click on Run. Browse your .int file, click OK and then you should see the output Hello world! inside an application output console.



So then that’s it. Go ahead and start writing your own COBOL programs from now. I will try to add to my list as I learn more. Till then enjoyy! 🙂