Tutorial #18: Calling a PL/SQL procedure in ODI

Hello friends!

Today, I will be demonstrating a short tutorial that will help you call a PL/SQL procedure using Oracle Data Integrator (ODI).

Pre-requisites: Oracle Data Integrator 11g, Oracle 10g Express Edition with *SQL Plus.

I will be creating a procedure that will insert a new row in my database table in Oracle. Through ODI, I will execute/call the same procedure!

The steps are as follows:

Step 1: Create table Product

Open *SQL Plus and connect to the database using your credentials. Now, let’s create Product table.

create table Product(prod_id number, prod_name varchar2(30), price number);

Step 2: Create PL/SQL procedure

Let’s write a simple procedure that will insert a new row in the Product table.

create or replace procedure insert_proc as
begin
insert into Product values(104,'Nokia',2440);
end;
/

Once the procedure has been created successfully, open Oracle Data Integrator studio and connect to your work repository. Using ODI we will simply call the procedure.

If you haven’t created your work repository in ODI please refer this.

Step 3: Create data server, physical and logical schema

To create a new data server, go to Topology navigator, under Technologies,right click Oracle and select new data server. Enter the correct details (including JDBC URL) as per your database connection and click on Save.

creating new data server

creating new data server

creating new data server

creating new data server

Now create physical schema by right clicking the data server and selecting new physical schema.

create physical schema

create physical schema

In the same way, create your logical schema. Make sure you select a Context. In my case I have selected Global.

create logical schema

create logical schema

Step 4: Create new project followed by new procedure

Create a new project named test_project under the Designer navigator. Go to First Folder–>Procedure and right click to create new procedure.

create new procedure

create new procedure

Run the procedure and check the Operator navigator for the status of the operation.

view operator status

view operator status

Finally, check if the new row is inserted by performing a select operation on your table in Oracle.

check product table

check product table

Karan Balkar About Karan Balkar
self proclaimed extraterrestrial fandroid, computer engineer, amateur gamer and die hard punk rock fan!

7 thoughts on “Tutorial #18: Calling a PL/SQL procedure in ODI

  1. Pingback: mens suits

  2. How can we send the data from one technology to another technology.??

    like: sending one table data of ORACLE to SQL server.

    Thanks,
    Santhosh

  3. let’s imagine i have only stored procedure, how can i insert the output of these procedure into target table using ODI

Leave a Reply

Your email address will not be published. Required fields are marked *