How to create ODI Procedures

1.Creating a Project

On the left, on the Designer tab, expand  Projects. On the Projects pull-down (three colored squares icon), select New Project. 

An unnamed Project panel opens on the right.

On the right, on the Definition tab, in Name, enter Proc-Crt-Tbl

Save your work by clicking

You should now see your new Project in the tree on the left.

 

2. Creating and Running a Procedure:

An ODI procedure is like a script. It may contain SQL and file operations. Using checkboxes for options, you can anticipate many common situations. 

To create and run a procedure, perform the following steps: 

On the left, on the Designer tab, expand  Projects > Proc-Crt-Tbl > First Folder > Procedures. Right-click Procedures and select New Procedure.

On the right, on the Procedure panel, on Name enter Procedure-create-populate-table. In the Target Technology pull-down, choose Oracle.

2.2 Task Drop Table

On the Tasks tab, click Add (the green plus).

It creates a blue line on the top, but the blue line gets populated by your entering information on the bottom Properties panel, you don’t enter anything on the blue line itself.

In the Properties panel below, in the General section, in Name enter Drop Table. Select Ignore Errors to cover the situation where you try to drop a non-existent table.

In the Target Command section, select the following pull-downs:

·         Technology = Oracle

·         Context = Global

·         Schema = Scott

·         In Command, enter DROP TABLE hr.planets in the big box. Notice there is no punctuation after the table name, no semi-colon, nothing.

 

 

 

2.3 Task Create Table:

 

 2.3 Task Populate Table:

3.Running Procedures

On the left, on the Designer tab, expand  Projects > Proc-Crt-Tbl > First Folder > Procedures. Right-click Procedure-create-populate-table and select Run.

On the left, on the Operator tab, expand Date > Today > Procedure-create-populate-table-datetime > Procedure-create-populate-table-datetime and look for the green circle check marks .

 

Verify that the table was actually populated using a tool outisde of ODI.