Using ODI 12c’s new mapping designer!

In my previous post, I went through the installation of Oracle Data Integrator 12c on the Windows platform. In earlier versions of ODI, we used Interfaces to load a single target datastore from one or more source datastores. Starting from 12c, the concept of Mappings is introduced.

A mapping comprises of a logical design and at least one physical design. A mapping can have many targets, of any technology and can be arbitrarily complex. One can build reusable mappings and use them in other mappings or other reusable mappings (somewhat similar to yellow interfaces). Let’s understand how mappings work through a simple example!

Pre-requisites: Oracle Data Integrator 12c, Oracle database 10g XE

Step 1: Create database tables

Login to any work schema and create two tables in your Oracle database as follows!

create table mappingsrc(empid int primary key, empname varchar(20));
create table mappingtarget(empid int primary key, empname varchar(20));

Add a few dummy records to the mappingsrc table only.

Step 2: Create mapping

Now, open ODI Studio and in any of your existing projects create a new mapping called test_mapping as follows!

mapping_1

Add the source and the target mappings in the Logical Designer.

mapping_2

mapping_3

Once you have completed the mapping, don’t forget to validate it for any errors!

validate_mapping

Finally, run the mapping and check the status of the operation under the Operator tab.

output_1

output_2

That’s it for this post. Stay tuned for more! 🙂

Installing Oracle Data Integrator 12c!

Hi everyone!

Oracle recently announced the latest version of its data integration products by introducing Oracle Data Integrator 12c and Oracle GoldenGate 12c. It is one of the biggest releases of the company this year and provides features that include improved performance, increased productivity and simplified deployment.

Through this post, we will learn how to install Oracle Data Integrator 12c on Windows. The following are some of the new features added to ODI 12c.

  • Declarative Flow-Based User Interface
  • Reusable Mappings
  • Multiple Target Support
  • Step-by-Step Debugger
  • Runtime Performance Enhancements
  • Oracle GoldenGate Integration Improvements
  • Standalone Agent Management with WebLogic Management Framework
  • Integration with OPSS Enterprise Roles
  • XML Improvements
  • Oracle Warehouse Builder Integration
  • Unique Repository IDs
  • Pre-requisites: Windows XP (32 bit) Service Pack 3

    Step 1: Download Oracle Data Integrator 12c

    Go to the downloads page for Oracle Data Integrator and download version 12c for all platforms (including 64 bit).

    Step 2: Run installer

    Once you have downloaded the software, you need to run the installer to begin the setup process. Follow the below steps for better understanding!

    installation_step_1

    installation_step_2

    installation_step_3

    installation_step_4

    installation_step_5

    This completes the installation. Now open ODI Studio from within the application directory. Further, you can choose to store passwords in Wallet.

    running_odi_1

    running_odi_2

    running_odi_3

    Reference: Oracle blog

    Tutorial #60: Load data from Oracle table to XML using OdiSqlUnload

    Hi everyone!

    It’s time for yet another ODI tutorial. Through this post, we will learn how to export the data from an Oracle table to an XML file. To implement this task we would be using the built in tool called OdiSqlUnload that comes shipped with Oracle Data Integrator.

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

    Step 1: Create table in Oracle

    Create a new table called Department in your work repository and insert a few dummy values!

    create table Department (DeptId int primary key, DeptName varchar(20));
    
    insert into Department values(201, 'Computers');
    insert into Department values(202,'Electronics');
    insert into Department values(203,'Mech');
    

    Step 2: Create XML file

    Make a new blank XML file called sample.xml. Do not add any contents to it as it would be done using a built in tool. 🙂

    Step 3: Create Package and use OdiSqlUnload

    Open ODI Studio and connect to your work repository. Create a new package named table_to_xml in any of your existing projects!

    Now, drag and drop the OdiSqlUnload tool under the Files section.

    create_package_drag_tool

    Add the required details and finally save and run the package. Check the status of the operation under the Operator tab. If the operation is successful you should see the XML file now populated with data from your Oracle table.

    add_required_fields

    output

    Output

    <?xml version="1.0" encoding="ISO-8859-1"?>
    
    <!-- File generated at 16/10/13 8:03 PM -->
    
    <table>
        <row>
          <DEPTID><![CDATA[201]]></DEPTID>
          <DEPTNAME><![CDATA[Computers]]></DEPTNAME>
        </row>
        <row>
          <DEPTID><![CDATA[202]]></DEPTID>
          <DEPTNAME><![CDATA[Electronics]]></DEPTNAME>
        </row>
        <row>
          <DEPTID><![CDATA[203]]></DEPTID>
          <DEPTNAME><![CDATA[Mech]]></DEPTNAME>
        </row>
    </table>
    

    Demystifying agents in Oracle Data Integrator!

    Hello folks!

    A little background

    Agents are basically services written in Java running on a specific TCP/IP port that carry out the execution of Interfaces, Packages etc. In previous versions of ODI namely ODI 10g one only had the standalone agents at his disposal. Ever since ODI 11g was introduced, the concept of Java EE agents came into the picture. Overall, both these agents are (almost) the same piece of code. The differences in them is what we will learn ahead!

    The tale of two Agents

    A Standalone agent will run on top of a Java machine or any machine with a JVM implementation. It is easier to deploy and we can create multiple such agents. The question that arises is how many would you keep on creating? In an environment that demands scalability, standalone agents would eventually fail. In such situations we use Java EE agents. Java EE agents are deployed as a Weblogic server application. Unlike standalone agents, they utilize the clustering and connection pooling features of the Weblogic server. They are a bit complex to deploy but promise to offer enterprise wide scalability. Standalone agents use the built-in ODI load balancing feature and do not support clustering and connection pooling.

    Scenario

    Let’s say you want to run an ODI Scenario that populates hundreds of records from the source table to the target table. You create two or three Standalone agents running on different ports (load balanced) that carry out the execution of the scenario. Now, the agent always first connects to the master repository, obtains the connection information to the work repository, connects to the work repository and then finally executes the Scenario. Each time the standalone agent tries to connect, it creates a new connection request to the database. As opening/closing of database is an expensive process, at some point in time there might exist a connection overhead resulting in slow down of the operation. A better option then would be to use a Java EE agent that is by default highly scalable, available and supports connection pooling.

    odi_agents

    What can we conclude?

    Finally, it is the user that has to decide which type of Agent is required. It is quite possible for both the agents to exist simultaneously in the same ODI architecture. The main difference is how you install them, in WLS or on top of JVM.

    Source: What’s new in ODI 11g – Part 1

    Tutorial #56: Beauty and the beast!

    In the past, I have written tutorials on Oracle Data Integrator as well as Android. Today, I thought let’s bring these two technologies together to create a mobile application that will perform the same functions that are done normally through ODI Studio.

    How about executing a Scenario from your mobile? or creating a Project, Model by entering it’s name and clicking on a Submit button. Sounds cool right? 🙂

    Pre-requisites: Eclipse IDE, Android SDK , Oracle Data Integrator (build version 11.1.1.7.0), RESTEasy API

    To start off, lets create our client (Android) application that will be displaying the user interface and handling the various event actions!

    Open Eclipse IDE and create a new Android application project named AndroidRestEasyClient with package name com.example.

    OdiActivity.java

    package com.example;
    
    import java.util.ArrayList;
    import java.util.List;
    import org.apache.http.HttpEntity;
    import org.apache.http.HttpResponse;
    import org.apache.http.NameValuePair;
    import org.apache.http.client.HttpClient;
    import org.apache.http.client.methods.HttpGet;
    import org.apache.http.client.methods.HttpPost;
    import org.apache.http.impl.client.DefaultHttpClient;
    import org.apache.http.message.BasicNameValuePair;
    import org.apache.http.util.EntityUtils;
    import android.app.Activity;
    import android.content.Intent;
    import android.os.AsyncTask;
    import android.os.Bundle;
    import android.util.Log;
    import android.view.View;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.Spinner;
    import android.widget.TextView;
    import android.widget.Toast;
    
    
    public class OdiActivity extends Activity
    {
    
    	private String connectUrl = "http://10.0.2.2:8081/RestEasyServices/connect";
    	private String projectUrl= "http://10.0.2.2:8081/RestEasyServices/project/";
    	private String scenarioUrl= "http://10.0.2.2:8081/RestEasyServices/scenario/";
    	
    	private String projname;
    	private String result;
    	private Spinner s;
    	private String scenname;
    	
    	private EditText projectName;
    	private Button sendMail,odiConnect,createProject,executeScen;
    	private TextView tv;
    	
    	    @Override
            protected void onCreate(Bundle savedInstanceState) 
            {
                super.onCreate(savedInstanceState);
                setContentView(R.layout.activity_odi);
         
                projectName= (EditText)findViewById(R.id.txtProject);
                tv= (TextView)findViewById(R.id.textView1);
            
                sendMail= (Button)findViewById(R.id.btn1);
                createProject=(Button)findViewById(R.id.createProject);
                executeScen=(Button)findViewById(R.id.executeScenario);
            
                s = (Spinner)findViewById(R.id.spinner1);
            
                odiConnect= (Button)findViewById(R.id.connect);
             
            
                odiConnect.setOnClickListener(new View.OnClickListener() 
                {
    		       @Override
    		         public void onClick(View v) 
    		         {
    			     // TODO Auto-generated method stub		
    			     new connectODI().execute(connectUrl);
    		         }
    	        });
            
            
                sendMail.setOnClickListener(new View.OnClickListener() 
                {
    	 	     @Override
    		     public void onClick(View v) 
    		     {
    			  // TODO Auto-generated method stub 
    			  Intent email = new Intent(Intent.ACTION_SEND);
    			  email.putExtra(Intent.EXTRA_EMAIL, new String[]{"yourusername@gmail.com"});		  
    		      email.putExtra(Intent.EXTRA_SUBJECT, "Test ODI using Android");
    			  email.putExtra(Intent.EXTRA_TEXT, "A new project was created through Android. Please check ODI Studio");
    			  email.setType("message/rfc822");
    			  startActivity(Intent.createChooser(email, "Choose an Email client :"));		   
    		     }
    	        });
            
                createProject.setOnClickListener(new View.OnClickListener() 
                  {	 		
    		      @Override
    		       public void onClick(View v) 
    			     {
    				   //TODO Auto-generated method stub
    				   projname= projectName.getText().toString().trim();
    				   
    				   if(projname!=null)
    				   {
    					   
    					   new projectODI().execute(projectUrl);
    					   Toast.makeText(getApplicationContext(), "Done...........", Toast.LENGTH_LONG).show();
    				  
    				   }
    				   
    				   else
    					   Toast.makeText(getApplicationContext(), "Please enter a Project name...!", Toast.LENGTH_LONG).show();		   
    			    }
    		     });
            
            
                executeScen.setOnClickListener(new View.OnClickListener() 
                  {
    		      @Override
    			  public void onClick(View v) 
    			    {
    				// TODO Auto-generated method stub
    				scenname= s.getSelectedItem().toString().trim();
    				
    				if(scenname!=null)
    				   {
    					   
    					   new scenarioODI().execute(scenarioUrl);
    					   Toast.makeText(getApplicationContext(), "Done...........", Toast.LENGTH_LONG).show();
    				  
    				   }
    				   
    			    }
    		    });
                    
        }// end Oncreate()
            
    
    	 private class connectODI extends AsyncTask<String, Void, Void>
    	    {
    		 
    	    	protected void onPreExecute() 
    	    	{
                         Toast.makeText(getApplicationContext(), "Executing...", Toast.LENGTH_LONG).show();
    
    	    	}
    
    	    	
    	    	protected void onPostExecute(Void unused)
    	    	{
    
    	    	Toast.makeText(getApplicationContext(), "Done...........", Toast.LENGTH_LONG).show();
    	    	tv.setText("Status :" +result);
    	    		
    	    	}
    
    			@Override
    			protected Void doInBackground(String... params)
    			{
    				try
    				{
    
    			    	HttpClient httpclient = new DefaultHttpClient();		    	    	
             		    HttpPost httppost = new HttpPost(connectUrl);
    			    	HttpResponse response =httpclient.execute(httppost);	    	
    			    	HttpEntity resp_entity =response.getEntity();
    			    	
    			    	System.out.println("RESPONE : " +response.toString());
    			    	
    			    	result = EntityUtils.toString(resp_entity);
    			    	
    			    	System.out.println("RESULT : " +result);
    	    	
    			   }
    			   catch(Exception e)
    			   {
    				Log.e("ERROR_OCCURRED", "Error in http connection "+e.toString());
    
    			   }
    
    			    	return null;
    			}
    
    			
    	   }//end class
    	
    	
    	 private class projectODI extends AsyncTask<String, Void, Void>
    	    {
    		 
    	    	protected void onPreExecute() 
    	    	{  
                          Toast.makeText(getApplicationContext(), "Executing...", Toast.LENGTH_LONG).show();
    	    	}
    
    	    	
    	    	protected void onPostExecute(Void result)
    	    	{
    	    		System.out.println("Done.........");
    	  
    	    	}
    
    			@Override
    			protected Void doInBackground(String... params)
    			{
    				try
    				{
    
    			    	HttpClient httpclient = new DefaultHttpClient();    	
    			    	System.out.println("Sending project name : "+projname);
    				    
    			    	projectUrl = "http://10.0.2.2:8081/RestEasyServices/project/" + projname;
    			    	    
    			    	HttpGet request = new HttpGet(projectUrl);
    			    	
    			    	List<NameValuePair> params2 = new ArrayList<NameValuePair>();
    			    	params2.add(new BasicNameValuePair("param", projname));
    			    	    	
    			    	HttpResponse response =httpclient.execute(request); 	
    			    	HttpEntity resp_entity =response.getEntity();
    			    	
    			    	System.out.println("RESPONSE : " +response.toString());
    			    	
    			    	String result = EntityUtils.toString(resp_entity);
    			    	
    			    	System.out.println("RESULT : " +result);
    
    			    	if(result!=null)
    			    		System.out.println("Project created succesfully...");
    			    	
    			    }
    				catch(Exception e)
    				{
    
    			    	Log.e("ERROR_OCCURRED", "Error in http connection "+e.toString());
    
    			    }
    
    			    	return null;
    			}
    
    			
    	  }//end class	 
    	 
    	 private class scenarioODI extends AsyncTask<String, Void, Void>
    	    {
    		 
    	    	protected void onPreExecute() 
    	    	{       
                       Toast.makeText(getApplicationContext(), "Executing...", Toast.LENGTH_LONG).show();
    	    	}
    
    	    	
    	    	protected void onPostExecute(Void result)
    	    	{
    	    			System.out.println("Done.........");
    	    
    	    	}
    
    			@Override
    			protected Void doInBackground(String... params)
    			{
    				try
    				{
    
    			    	HttpClient httpclient = new DefaultHttpClient();
    		    
    			    	scenarioUrl = "http://10.0.2.2:8081/RestEasyServices/scenario/" + scenname;
    			    				    
    			    	HttpGet request = new HttpGet(scenarioUrl);
    			    	
    			    	List<NameValuePair> params2 = new ArrayList<NameValuePair>();
    			    	params2.add(new BasicNameValuePair("param", scenname));
    			    		    	
    			    	HttpResponse response =httpclient.execute(request);		    	
    			    	HttpEntity resp_entity =response.getEntity();
    			    	
    			    	System.out.println("RESPONSE : " +response.toString());
    			    	
    			    	String result = EntityUtils.toString(resp_entity);
    			    	
    			    	System.out.println("RESULT : " +result);
    
    			    	if(result!=null)
    			    		System.out.println("Scenario executed succesfully...");
    			    	
    			    }
    				catch(Exception e)
    				{
    
    			    	Log.e("ERROR_OCCURRED", "Error in http connection "+e.toString());
    
    			    }
    
    			    	return null;
    			}
    
    	  }//end class
    	
    } //end Activity class
    
    
    

    activity_odi.xml

    <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
        xmlns:tools="http://schemas.android.com/tools"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:paddingBottom="@dimen/activity_vertical_margin"
        android:paddingLeft="@dimen/activity_horizontal_margin"
        android:paddingRight="@dimen/activity_horizontal_margin"
        android:paddingTop="@dimen/activity_vertical_margin"
        tools:context=".OdiActivity">
     
        <TextView 
            android:id="@+id/textView1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"/>
        
        <Button 
            android:id="@+id/connect"
            android:layout_below="@+id/textView1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/connect"/>
     
        <EditText 
            android:id="@+id/txtProject"
            android:layout_below="@+id/connect"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:hint="@string/createProject"/>
        
         <Button 
            android:id="@+id/createProject"
            android:layout_toRightOf="@+id/txtProject"
            android:layout_below="@+id/connect"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/project"/>
    	    
        
         <EditText 
            android:id="@+id/txtModel"
            android:layout_below="@+id/txtProject"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:hint="@string/createModel"/>  
            
        <!--  Select Scenario -->    
        <Spinner 
            android:id="@+id/spinner1"
            android:layout_below="@+id/createModel"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:entries="@array/scenario_list"
            android:prompt="@string/scenario"/>
        
         <Button 
            android:id="@+id/executeScenario"
            android:layout_below="@+id/spinner1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/execute"/>
    	
         <Button 
            android:id="@+id/btn1"
            android:layout_below="@+id/executeScenario"
            android:layout_centerHorizontal="true"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/mail"/>
       
    </RelativeLayout>
    
    

    AndroidManifest.xml

    <?xml version="1.0" encoding="utf-8"?>
    <manifest xmlns:android="http://schemas.android.com/apk/res/android"
        package="com.example"
        android:versionCode="1"
        android:versionName="1.0">
    
        <uses-permission android:name="android.permission.INTERNET" />
        <uses-permission android:name="android.permission.CHANGE_WIFI_STATE" />
        <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
        <uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />
      
        <uses-sdk
            android:minSdkVersion="8"
            android:targetSdkVersion="10"/>
    
        <application
            android:allowBackup="true"
            android:icon="@drawable/ic_launcher"
            android:label="@string/app_name"
            android:theme="@style/AppTheme">
            <activity
                android:name="com.example.OdiActivity"
                android:label="@string/app_name">
                <intent-filter>
                    <action android:name="android.intent.action.MAIN" />
                    <category android:name="android.intent.category.LAUNCHER" />
                </intent-filter>
            </activity>
        </application>
    
    </manifest>
    

    Here we are done with the client side code. Don’t run the Android application yet! Let’s begin the server side implementation by creating a new Dynamic Web Project named RestEasyServices. Follow this tutorial for more information.

    To connect to ODI, let’s create the ConnectService as follows!

    ConnectService.java

    package com.example;
    
    import java.sql.SQLException;
    import javax.ws.rs.GET;
    import javax.ws.rs.POST;
    import javax.ws.rs.Path;
    import javax.ws.rs.Produces;
    import javax.ws.rs.core.MediaType;
    import javax.ws.rs.core.Response;
    import oracle.odi.core.OdiInstance;
    import oracle.odi.core.config.MasterRepositoryDbInfo;
    import oracle.odi.core.config.OdiInstanceConfig;
    import oracle.odi.core.config.PoolingAttributes;
    import oracle.odi.core.config.WorkRepositoryDbInfo;
    import oracle.odi.core.persistence.transaction.ITransactionStatus;
    import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;
    import oracle.odi.core.security.Authentication;
    
    
    @Path("connect")
    public class ConnectService 
    {
    	
    	private OdiInstance odiInstance;
    	@SuppressWarnings("unused")
    	private ITransactionStatus trans;
    	
    
    		@GET
    		@POST
    		@Produces(MediaType.TEXT_HTML)
    				public Response connect() throws SQLException 
    				{
    
    				
    					String result=null;
    					
    					try
    					{
    						
    						
    						MasterRepositoryDbInfo masterInfo = new MasterRepositoryDbInfo("jdbc:oracle:thin:@localhost:1522:orcl", 
    									"oracle.jdbc.OracleDriver",	"ODIMR", "password".toCharArray(), new PoolingAttributes());
    						WorkRepositoryDbInfo workInfo = new WorkRepositoryDbInfo("WORKREP121", new PoolingAttributes());
    							
    						odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterInfo, workInfo));
    						 
    						Authentication auth = odiInstance.getSecurityManager().createAuthentication("SUPERVISOR", "password".toCharArray());
    					    odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth);
    					    trans = odiInstance.getTransactionManager().getTransaction(new DefaultTransactionDefinition());
    					
    					    
    					    if(odiInstance!=null)
    					    	result = "Connected to ODI";
    					    
    					    return Response.status(200).entity(result).build();
    					}
    					catch(Exception e)
    					{
    						e.printStackTrace();
    					}
    
    					result = "Not Connected to ODI";
    				    return Response.status(200).entity(result).build();
    									
    				}
    	}
    		
    

    Similarly, one can create Service classes for Project and Scenario. Once this is done, run the client side Android application. Connect to ODI and then try creating a new Project as follows!

    android_app_1

    odi_1

    Also, try executing a Scenario as follows!

    android_app_2

    odi_2

    Finally, this completes the tutorial. Hope it helps! 🙂 Happy programming!

    Tutorial #55: Load data from one Excel sheet to another in Oracle Data Integrator

    Hello friends!

    While trying to load data from an Excel sheet to a table in Oracle, many a times ODI developers come across the “Invalid fetch size” or the “ResultSet is closed” errors upon execution of their Interface. This issue has been discussed in many questions on the Oracle Forums website. Good news is that this bug has now been resolved.

    In order to resolve these errors, a common solution is as follows!

    1. Try using ODI 11g (version 11.1.1.3) or below

    2. In case you are using a higher version of ODI say 11.1.1.5, you need apply a Patch 13528165 on top of 11.1.1.5 as mentioned by Rickson Lewis over here

    In this tutorial I will explain how data from one Excel spreadsheet is loaded into another using Oracle Data Integrator!

    Pre-requisites: ODI 11.1.1.3, MS Excel 2007 software

    I will be using two Excel sheets namely source.xlsx and target.xlsx respectively. The source Excel sheet is populated as follows!

    source_excel

    Step 1: Create data server, physical & logical schemas for both Excel sheets

    Step 2: Create data models and reverse engineer

    model_creation

    Step 3: Create a new Interface

    Create a new Interface in any of existing projects named excel_to_excel as follows!

    interface_1

    interface_2

    interface_3

    interface_4

    Finally, run the Interface. Check the status of the operation under the Operator tab. You should see the target Excel sheet now populated! 🙂

    target_excel

    output

    Using Groovy in Oracle Data Integrator

    Hello everyone!

    Using the ODI SDK, developers can automate the creation of interfaces, models etc. However, to do so they need to have an external tool like Eclipse or any other Java IDE for writing the code. Starting from Oracle Data Integrator 11.1.1.5, a new scripting language called Groovy has been introduced. It basically provides an environment for creating, editing and running Groovy scripts within the ODI Studio.

    Like Eclipse, it also supports all standard features of a code editor such as syntax highlighting and common code editor commands. Open ODI Studio and you can find the Groovy editor on the Tools menu (Tools –> Groovy –> New Script)

    groovy_editor_1

    In this post, we will write a simple Groovy script that is used for Studio UI Automation!

    Let’s say I want the Find option under the Search menu to get automatically clicked without user intervention. To implement this I will use the following Groovy script.

    import javax.swing.JMenuItem;
    import javax.swing.JMenu;
    import oracle.ide.Ide;
     
    JMenu mn = ((JMenu)Ide.getMenubar().getGUI(false).getComponent(3));
    ((JMenuItem)mn.getMenuComponents()[0]).doClick()
    

    Run the above script by clicking on the green coloured Execute button. You should see the Search Box automatically appearing in the Studio.

    groovy_editor_2

    The Groovy scripting language has a Java-like syntax and is designed for the Java platform. At present, Groovy can also be used in Knowledge Modules and ODI Procedures. We will learn more about Groovy in the upcoming tutorials! 🙂

    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 11.1.1.5.0)

    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.

    create_scenario

    Step 2: Run ODI console

    Start the Admin Server in running mode.

    server_running

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

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

    snap_4

    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.

    running_scenario_using_console

    output

    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 11.1.1.5.0), 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

    weblogic_server_download

    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!

    configure_domain_1

    configure_domain_2

    Make sure you select the option Oracle Data Integrator – Console

    configure_domain_3

    configure_domain_4

    configure_domain_5

    configure_domain_6

    configure_domain_7

    configure_domain_8

    configure_domain_9

    configure_domain_10

    configure_domain_11

    Add the Admin Server by clicking the arrow button.

    configure_domain_12

    configure_domain_13

    configure_domain_14

    configure_domain_15

    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.

    server_running

    Enter the address:

    http://localhost:7001/console/login/LoginForm.jsp

    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)

    login_weblogic

    odi_console_1

    odi_console_2

    odi_console_3

    odi_console_4

    Enter SUPERVISOR credentials for logging into the ODI console!

    odi_console_5

    odi_console_6

    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 11.1.1.5.0), 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,

    C:\oracle\Middleware\user_projects\domains\domain-name\autodeploy

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

    existing_domain

    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!

    data_server

    logical_schema

    physical_schema

    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.

    startweblogic_server

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

    create_data_source

    Now click Next and follow the below steps!

    step_1

    step_2

    step_3

    step_4

    step_5

    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!

    import_skm_module

    generate_service_1

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

    generate_service_2

    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).

    test_service_1

    test_service_2

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

    test_service_3

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