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.
Now create physical schema by right clicking the data server and selecting new physical schema.
In the same way, create your logical schema. Make sure you select a Context. In my case I have selected Global.
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.
Run the procedure and check the Operator navigator for the status of the operation.
Finally, check if the new row is inserted by performing a select operation on your table in Oracle.
self proclaimed extraterrestrial fandroid, computer engineer, amateur gamer and die hard punk rock fan!