13 Procedures

Learn about procedures in Oracle Data Integrator, what they are and how you create them.

Topics

• What is a Procedure
• Create a Procedure

What is a Procedure

Learn about procedures in Oracle Data Integrator.

A procedure in Oracle Data Integrator (ODI) consists of a series of commands executed in sequence. Commands contain code that can be executed by database engines, the operating system where the Agent is running, or directly by ODI. You can also define options in the procedure to control its behavior at run time. A very useful property of procedures is that they are reusable and can be inserted into packages. Thus, just as procedures comprise commands, a package can contain several procedures as steps.

You use procedures in a package or a scenario, or you can run them directly from the Designer Navigator.

Here are some examples of the uses of procedures:

• The Email Administrator procedure contains a single command that calls the OdiSendMail email tool. You can then add an option to specify the email address. Thus, to change the email address of the administrator, you do not have to modify the procedure. You can even reuse the procedure in several places, each with different parameters to send emails to different people.
• You can create an ODI procedure to create an RDBMS table and populate it with data.

See Introduction to Procedures in Developing Integration Projects with Oracle Data Integrator.

See Using Procedures in Developing Integration Projects with Oracle Data Integrator.

Create a Procedure

Learn the steps to create a procedure in Oracle Data Integrator.

Use the following steps to create a procedure in ODI:

1. Create a Blank Procedure

   Like mappings and packages, procedures are contained within project folders.

2. Add Commands to a Procedure

   Now you have created a procedure that performs a series of tasks. However, it always performs the same tasks with the same parameters. To make the procedure more flexible, you can add options to it.

3. Add New Options to a Procedure

   Add options to a procedure to make it customizable. You do not need to define any options.

   These options function as parameters to the procedure. This means that you can use the same procedure in different environments with different parameters, without having to change any code.

   Procedure options function as parameters.

   Options can be used in two ways to control the way procedures are executed.

   • Check box options serve to skip individual commands in the procedure. For example, you can have an option that, if false, completely skips a command.
   • Value or text options are used in the text of commands to influence the behavior of the procedure at a smaller level. For example, the subject of an email can be a value option, which is then passed to the OdiSendMail tool.

   In both cases, you define a default value for the option. This is the value that is used if no other value is specified when the procedure is used. When you call the procedure in a package, you can override any of the default values to customize the procedure for that particular context.

4. Save and run the procedure.

   One of the most common ways to execute an ODI procedure is to run it from within a package. However, you can also execute a procedure manually for testing. You click the Execute button in the procedure window to do this. Default values for all options are used. You can, however, override the default values for the options. Click the procedure step, and then click the Options tab from the Properties pane. Here, you can select the values of options that are used when the procedure step is executed.

See Creating Procedures in Developing Integration Projects with Oracle Data Integrator.

An example of creating, populating and running a procedure is in Section 2 - Creating and Running a Procedure in the Oracle By Example tutorial Oracle Data Integrator 12c - Creating Procedures and Scenarios.

Create a Blank Procedure

Learn how to create a blank procedure in Oracle Data Integrator

Use the following steps to create a blank procedure in ODI:

1. Navigate to the Project and Project Folder where you want to create the procedure. Right-click the Procedures node and select New Procedure.
2. Provide the procedure a meaningful name and a description. You may want to include any limitations the procedure has in the description.
3. (optional) Set the default target and source technologies. If you don't set these technologies, you must set them individually for each command. You can always override these technologies for each individual step.

Add Commands to a Procedure

Learn how to add commands to a procedure in Oracle Data Integrator

Add one or more commands to make the procedure do something useful. You can create a procedure with only one command, but you often want more than one. Remember that commands are always performed in order.

1. Click the Tasks tab of the procedure window.
2. Click the Add Command (green plus) icon. A window for the command appears.
3. Enter a name for the command. This name appears in the Operator navigator when you execute the procedure, so you should try to make it specific and meaningful.
4. Select the Ignore Errors check box so that the procedure runs even if this command fails.
5. Select the appropriate options for the Command on Target and Command on Source options. (Note that a command can execute code in two places (Source and Target) within the same command.) Click Command on Target first.
6. Select the technology used in the command. This affects the code that you can use and how ODI generates its code.
7. Keep the Context as <Execution Context>, so that the user can select the context at the time the procedure is executed. The Schema and Context fields represent the logical schema and execution context for database queries.

Add New Options to a Procedure

Learn how to add options to new procedures in Oracle Data Integrator

Add new options to make the procedure customizable. You do not need to define any options.

1. Click the Options tab.
2. In Properties, enter the name as it appears in the list of options when you execute the procedure. Enter a description.

   The value that you enter in the Description field is displayed when you select the options that trigger a command in a procedure. It should be a short reminder of what the option does. Complete the Help field only when creating a Knowledge Module. It enables you to provide a longer description of the option, including any side effects or special notes.

3. Select the option type.

   This can be Boolean for an option that determines whether a given step is executed or not, Value for a numeric value, or Text for a text string. Complete the Default Value field. The default value specified for an option is the value that is used if you do not specifically set it in a package.

4. Save and run the procedure.