Use to create and configure your procedures in Oracle Data Integrator.
A procedure is a set of commands that can be executed by an agent. These commands concern all technologies accessible by Oracle Data Integrator (OS, JDBC, JMS commands, and so forth).
The Procedure Editor has the following tabs:
| Properties | Description |
|---|---|
| Name | Name of the procedure, as it appears in the user interface. |
| Multi-Connections | Shows if the procedure accesses paired data servers. If this check box is selected, data loading commands can be exchanged between two data servers, of different technologies if necessary. For example, on procedure commands, you can specify a command "SELECT ...." in one database and a command "INSERT" into another database to transfer the whole result. |
| Source Technology | Source technology used by default on procedure commands. This information is only available if the procedure is a "Multi-connections"-type and if the technology has existing dataservers defined or is of type AAA, BBB, CCC, or DDD. |
| Target Technology | Default technology to which the commands of this procedure are addressed. This information is only available if the technology has existing dataservers defined or is of type AAA, BBB, CCC, or DDD. |
| Use Unique Temporary Object Names | If checked, multiple instances of same procedure can be executed in parallel without interfering with each other. |
| Remove Temporary Objects On Error | Indicates if the temporary objects created with the unique names should be deleted if the procedure ends up in Error. This allows to cleanup the temporary objects automatically on Error.
If unchecked, and unique temporary objects names are used, then temporary objects created with unique names are not cleaned automatically and should be cleaned manually. |
| Description | Detailed description of the procedure. |
A procedure is made up of several commands. The Tasks tab shows a list of these commands, which you can create, delete, and arrange by using the buttons in the toolbar. To edit a task, select the task, and edit in the Properties Editor. To update any information directly in the task list, click a particular field and update it.
| Properties | Description |
|---|---|
| Name | Name of the command, as it appears in the user interface. |
| Cleanup | If procedure flag Remove Temporary Objects OnError is set to true, the tasks flagged as Cleanup tasks get executed even on error. |
| Log Counter | Shows which counter (Insert, Update, Delete or Errors) records the number of rows processed by this command. |
| Log Level | Logging level of the command. At execution time, commands can be kept in the log based on their log level. |
| Ignore Errors | Shows that the procedure is not interrupted in case of an invalid return code. If this box is selected, the procedure command goes into "warning" instead of "error", and the procedure is not be stopped. |
| Log Final Command | Select to log final code for source and target commands. This improves the readability of logs which have a lot of runtime parameters.
Use this option only for tasks that do not contain passwords or other sensitive data.nad |
| Properties | Description |
|---|---|
| Technology | Technology on which the command is executed.
Note: To use Oracle Data Integrator Tools (commands) in KM procedure commands or procedures commands, you must set the technology to ODI Tools. To use OS commands or to execute external programs, you must set the technology to Operating System |
| Transaction Isolation | Transaction isolation level for Select orders. The isolation levels shown are those of the SQL ISO. They are not supported by all data servers.
The levels of isolation are: Default: The transaction keeps the connection's isolation level. Read Uncommitted: The transaction can read data not committed by another transaction. Read Committed: The transaction can only read data committed by other transactions (in general, this is the default mode of many data servers). Repeatable Read: The transaction is certain to read the same information if it executes the same SQL query several times, even if the rows have been modified and committed by another transaction in the meantime. Serializable: The transaction is certain to read the same information if it executes the same SQL query several times, even if the rows have been modified, deleted, or created and committed by another transaction in the meantime. |
| Context | Execution context of the query. If "Execution context" is left, the command will be executed in the execution context chosen on launching or on the step. |
| Schema | Logical schema for execution. The logical schema, linked to the context, allows the physical execution schema to be defined. |
| Transaction | You can execute commands on several concurrent transactions (numbered from 0 to 9) or work off-transaction by choosing the option Autocommit. |
| Commit | If your command is being executed in a transaction (numbered 0 to 9), you can decide to continue or to commit the current transaction according to the following modes:
No Commit: The transaction is not committed. In this case, it can be committed in a later command. If a session ends normally, all transactions are committed by default. Commit: The transaction is committed. Commit 1000 rows: Oracle Data Integrator commits every 1000 records processed. This choice is only possible on a loading procedure command, that is, one containing a source command returning a result set and a destination command that carries out inserts. |
| Command | Text of the command expressed in a native language or in a standard language (such as SQL, PL/SQL, Transact-SQL, shell, and so forth). You can use the Expression Editor, accessible through the button to the right of the text.
Warning: If the command is made of random characters, then the procedure is encrypted. For more details about encryption and decryption, see "Encrypting and Decrypting Procedures" in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator |
| Properties | Description |
|---|---|
| Technology | Technology on which the command is executed.
Note: To use Oracle Data Integrator Tools (commands) in KM procedure commands or procedures commands, you must set the technology to ODI Tools. To use OS commands or to execute external programs, you must set the technology to Operating System |
| Transaction Isolation | Transaction isolation level for Select orders. The isolation levels shown are those of the SQL ISO. They are not supported by all data servers.
The levels of isolation are: Default: The transaction keeps the connection's isolation level. Read Uncommitted: The transaction can read data not committed by another transaction. Read Committed: The transaction can only read data committed by other transactions (in general, this is the default mode of many data servers). Repeatable Read: The transaction is certain to read the same information if it executes the same SQL query several times, even if the rows have been modified and committed by another transaction in the meantime. Serializable: The transaction is certain to read the same information if it executes the same SQL query several times, even if the rows have been modified, deleted, or created and committed by another transaction in the meantime. |
| Context | Execution context of the query. If "Execution context" is left, the command will be executed in the execution context chosen on launching or on the step. |
| Schema | Logical schema for execution. The logical schema, linked to the context, allows the physical execution schema to be defined. |
| Transaction | You can execute commands on several concurrent transactions (numbered from 0 to 9) or work off-transaction by choosing the option Autocommit. |
| Commit | If your command is being executed in a transaction (numbered 0 to 9), you can decide to continue or to commit the current transaction according to the following modes:
No Commit: The transaction is not committed. In this case, it can be committed in a later command. If a session ends normally, all transactions are committed by default. Commit: The transaction is committed. Commit 1000 rows: Oracle Data Integrator commits every 1000 records processed. This choice is only possible on a loading procedure command, that is, one containing a source command returning a result set and a destination command that carries out inserts. |
| Command | Text of the command expressed in a native language or in a standard language (such as SQL, PL/SQL, Transact-SQL, shell, and so forth). You can use the Expression Editor, accessible through the button to the right of the text.
Warning: If the command is made of random characters, then the procedure is encrypted. For more details about encryption and decryption, see "Encrypting and Decrypting Procedures" in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator |
| Properties | Description |
|---|---|
| Always Execute | If checked, the Task executes under any conditions. If not checked, the Task executes under the conditions set in the Options tab. |
| Name | Description |
|---|---|
| Expand All | Expands all the task groups. |
| Collapse All | Collapses all the task groups in task hierarchy. |
| Add | Adds a new task. |
| Delete | Deletes the selected task. |
| Up | Moves the selected task up in the list. |
| Down | Moves the selected task down in the list. |
| Move task to top | Moves the selected task to the top. |
| Move task to bottom | Moves the selected task to the bottom. |
| Select Columns | Adds a task property to the task table. |
A procedure may be parameterized when executed, by means of options. The Options tab allows all options to be displayed, as well as their default values. The options values specified in this tab are only used during the execution of the procedure.
| Properties | Description |
|---|---|
| Name | Name of the option. It is a common practice to use upper-case names with underscores ("_") such as DETECTION_STRATEGY. |
| Type | Datatype of the option.
Possible values are Boolean, Choice, Text, or Value. Boolean options can be true or false. A choice option allows you to choose the option value from a pre-defined list of option values. These values can be edited using the Edit Choice Dialog. Value options can hold an alphanumeric value up to 250 characters, and Text options can hold an alphanumeric value of unlimited length and are parsed for variable and sequence references. |
| Default Value | Value of the option that is set by default. |
| Condition Expression | Click the condition expression cell against a particular option to enter or edit the groovy script, which will determine if a procedure option should be disabled, enabled, shown, or hidden. |
| Description | Text that will be shown in the Oracle Data Integrator UI next to the option. Text should be short to be readable in a single line inside a table. |
| Name | Description |
|---|---|
| Add | Adds a new option.
Note: The Add Group option is not supported and is therefore disabled. |
| Delete | Deletes the selected option. |
| Move Up | Moves the selected option up in the list. |
| Move Down | Moves the selected option down in the list. |
| Move to top | Moves the selected option to the top of the list. |
| Move to bottom | Moves the selected option to the bottom of the list. |
| Select Column | Selects or deselects the columns to be displayed in the table. |
A procedure is executed on a context and an agent. Click Run in the menu bar to start the execution process.
The Execution tab is organized into the Direct Executions and the Scenario Execution tabs and shows the results of previous executions.
The Direct Executions tab contains the following elements:
| Properties | Description |
|---|---|
| Agent | Name of the agent that executed the procedure. Internal indicates that the agent used is the one built in the Oracle Data Integrator Studio. |
| Context | Execution context of the mapping. |
| Status | State of the procedure execution (Done, Error, Running, Waiting, Warning). |
| Start | Start date and time of execution of the procedure. |
| End | End date and time of execution of the procedure. |
| Duration | The time taken for execution of the procedure. |
| Return Code | Return code of the procedure. |
| Message | Procedure execution error message. |
The Scenario Execution tab contains the following elements:
| Properties | Description |
|---|---|
| Agent | Name of the agent that executed the scenario. Internal indicates that the agent used is the one built in the Oracle Data Integrator Studio. |
| Context | Execution context of the scenario. |
| Status | State of the procedure execution (Done, Error, Running, Waiting, Warning). |
| Start | Start date and time of the execution of the scenario. |
| End | End date and time of the execution of the scenario. |
| Duration | The time taken for the execution of the scenario. |
| Return Code | Return code of the scenario. |
| Message | Procedure execution error message. |
| Rows | Total number of rows processed by the scenario. |
| Inserts | Number of rows inserted during the scenario. |
| Updates | Number of rows updated during the scenario. |
| Deletes | Number of rows deleted during the scenario. |
| Errors | Number of rows in error in the scenario. |
"Creating Procedures" in the "Creating and Using Procedures, Variables, Sequences, and User Functions" chapter in Oracle Fusion Middleware Developing Integration Projects with Oracle Data Integrator.
Oracle Fusion Middleware Integrating Big Data with Oracle Data Integrator