Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)

B31278-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

21 Activities in Process Flows

After you design mappings that define the operations for moving data from source to target, you can create and define process flows. Use process flows to interrelate mappings and activities external to Warehouse Builder. For example, email, FTP commands, and operating system executables.

Using Activities in Process Flows

Use this section as a reference for all the activities. This section begins by categorizing activities by type. For detailed descriptions of each activity, see the alphabetical listing in the remainder of this section.

Activities that Represent Objects

Table 21-1 lists the activities that represent objects you previously created in Warehouse Builder. You can specify one or more incoming transitions. For outgoing transitions, you can use the success, warning, error, and unconditional transitions once each and then also define an unlimited number of complex condition transitions.

Table 21-1 Activities that Represent Objects

Icon Activity Brief Description
This illustration is described in the surrounding text.

Data Auditor

Adds to the process flow an existing data auditor monitor used in data profiling.

This illustration is described in the surrounding text.

Mapping

Adds an existing mapping to the process flow.

This illustration is described in the surrounding text.

Subprocess

Embeds an existing process flow within the process flow.

This illustration is described in the surrounding text.

Transform

Adds an existing transformation to the process flow.


Committing Data

When you add activities that represent design objects, the process flow evaluates each of these activities as a separate transaction. For example, when you add mapping activities, the process flow commits and rolls back each mapping independently. In this design, it is not possible to control all the mappings by one commit or rollback statement.

To collectively commit or rollback multiple mappings, consider designing the process flow with a Sqlplus activity associated with a script that calls each mapping. For instructions, see "Committing Mappings through the Process Flow Editor".

Utility Activities

Table 21-2 lists each utility activity and shows the associated icon.

Table 21-2 Utility Activities

Icon Activity Brief Description
This illustration is described in the surrounding text.

Assign

Assigns a value to a variable.

This illustration is described in the surrounding text.

Email

Sends an email. For example, send an email message about the status of activities in the process flow.

This illustration is described in the surrounding text.

File Exists

Use the File Exists activity to check if a file is located on a specified drive or directory.

This illustration is described in the surrounding text.

Manual

Halts a process flow and requires manual intervention to resume the process flow.

This illustration is described in the surrounding text.

Notification

Sends an email to a user and allows the user to select from a list of responses that dictates how the process flow proceeds.

This illustration is described in the surrounding text.

Set Status

Interjects a success, warning, or error status.

This illustration is described in the surrounding text.

Wait

Delays the progress of the process flow by a specified amount of time.


Control Activities

Table 21-3 lists the activities you use to control the process flow. The table shows the associated icon. It also lists the number of incoming and outgoing transitions allowed for each activity.

Table 21-3 Control Activities

Icon Activity Brief Description Incoming Transitions Outgoing Transitions
This illustration is described in the surrounding text.

AND

Specifies the completion of all incoming activities before starting another activity.

Two or more allowed. The number of incoming transitions must be less than or equal to the number of outgoing transitions from the upstream FORK.

Unconditional and complex transitions are not allowed.

This illustration is described in the surrounding text.

End (successfully)

Designates a path as being successful.

One or more allowed

Not allowed

This illustration is described in the surrounding text.

End (with errors)

Designates a path as ending in errors.

One or more allowed

Not allowed

This illustration is described in the surrounding text.

End (with warnings)

Designates a path as ending with warnings.

One or more allowed

Not allowed

This illustration is described in the surrounding text.

End Loop

Defines the end of a For Loop or While Loop

One or more allowed

One to For Loop or While Loop only

This illustration is described in the surrounding text.

For Loop

Use this activity with an End Loop to define constructs that repeat.

One from End Loop required plus more from other activities

One Loop condition and one Exit required

This illustration is described in the surrounding text.

FORK

Starts two or more activities after completing an activity.

One or more allowed

Two or more unconditional transitions only

This illustration is described in the surrounding text.

OR

Starts an activity after the completion of any of two or more specified activities.

Two or more allowed

One unconditional transition only

This illustration is described in the surrounding text.

Route

Defines exclusive OR and if-then-else scenarios.

   
This illustration is described in the surrounding text.

While Loop

Run other activities while a condition is true.

One from End Loop required plus more from other activities

One Loop condition and one Exit required


OS Activities

Table 21-4 lists the OS activities that can be initiated by a process flow.

Table 21-4 OS Activities

Icon Activity Brief Description
This illustration is described in the surrounding text.

FTP

Starts a file transfer protocol command during a process flow. For example, use the FTP activity to move data files to the computer where a mapping runs.

This illustration is described in the surrounding text.

Sqlplus

Runs a SQL*Plus script in a process flow.

This illustration is described in the surrounding text.

User Defined

Represents an activity that is not predefined and enables you to incorporate it into a process flow.


Because it is not desirable to allow a user have complete control over OS activities, Warehouse Builder allows you to determine which OS activities can be initiated by a process flow. This is primarily achieved by constraining the user's ability to execute operating system commands either by granting or revoking direct execution or by mandating that execution be performed through a third party, as described in Setting a Security Constraint. Further access control can be achieved using a proxy command and parameters, which can be used to secure all executions.

This security feature is controlled by setting properties in the Runtime.properties file in the $owb_home/owb/bin/admin directory. This file contains Control Center property values that run the Control Center service. This file is set to read-only at Control Center service startup. If you make changes to the file, then you must restart the Control Center service for the changes to take effect.

Setting a Security Constraint

By default, security_constraint for the OS activity commands are set to DISABLED:

property.RuntimePlatform.0.NativeExecution.FTP.security_constraint = DISABLED
property.RuntimePlatform.0.NativeExecution.Shell.security_constraint = DISABLED
property.RuntimePlatform.0.NativeExecution.SQLPlus.security_constraint = DISABLED

To enable an OS activity, you must set security_constraint to NATIVE_JAVA or Scheduler.

property.RuntimePlatform.0.NativeExecution.FTP.security_constraint = NATIVE_JAVA
property.RuntimePlatform.0.NativeExecution.Shell.security_constraint = NATIVE_JAVA
property.RuntimePlatform.0.NativeExecution.SQLPlus.security_constraint = NATIVE_JAVA

NATIVE_JAVA allows direct execution by the Control Center service and SCHEDULER forces execution through DBMS_SCHEDULER.

Setting a Proxy Command and Parameters

For each activity type, USER DEFINED (Shell), FTP, and SQLPlus, there are two properties. The proxy_command property and the proxy_parameter_list property (optional).

If a proxy command is specified, then that command is run instead of the user's specified command and parameters. The user specified command and parameters are passed as parameters to the proxy command following the proxy parameters. The proxy command then becomes the context in which the user's command is run.

The proxy_command property allows the proxy command to be specified.

To set a proxy command for the activities, set the proxy command as well as the proxy parameter list (optional) using the following command:

property.RuntimePlatform.0.NativeExecution.FTP.proxy_command
property.RuntimePlatform.0.NativeExecution.FTP.proxy_parameter_list
property.RuntimePlatform.0.NativeExecution.Shell.proxy_command
property.RuntimePlatform.0.NativeExecution.Shell.proxy_parameter_list
property.RuntimePlatform.0.NativeExecution.SQLPlus.proxy_command
property.RuntimePlatform.0.NativeExecution.SQLPlus.proxy_parameter_list

For example, to set a proxy command for Shell:

 property.RuntimePlatform.0.NativeExecution.Shell.proxy_command = /bin/proxy_sh
 property.RuntimePlatform.0.NativeExecution.Shell.proxy_parameter_list = ?-v?-n?

Note:

Ideally, only the Warehouse Builder administrator must have the rights to modify the Runtime.properties file. The users should be granted read-only permission.

AND

Use the AND activity to specify the completion of two or more activities before resuming the process flow.

This illustration is described in the surrounding text.
Description of the illustration and.gif

The AND activity can have two or more incoming transitions. To correctly design process flows with an AND activity, you must place a FORK activity upstream of the AND. Also, the number of transitions going into the AND activity must be less than or equal to the number of outgoing transitions from the upstream FORK. The FORK is the only activity that enables you to assign multiple unconditional transitions and therefore ensure the completion of multiple activities as required by the AND activity.

The AND activity enables you to aggregate the outcome of the upstream activities. If all the upstream activities return SUCCESS, then the AND activity returns SUCESSES. If any upstream activity returns an ERROR, then the AND activity returns ERROR, else a WARNING is returned. Any activity that does not have an outcome is considered to have returned SUCCESS. Use the SET_STATUS activity to force an outcome. The feature is particularly useful to test if a set of mappings that are running in parallel have all successfully completed.

Figure 21-1 shows the AND and FORK activities in a process flow. In this example, AND_ACTIVITY triggers downstream activities based on the completion of MAP1 and MAP2. The process flow is valid because the FORK activity has three outgoing transitions while AND_ACTIVITY has two incoming transitions. The process flow would also be valid if the transition and activities associated with MAP3 were deleted.

Figure 21-1 AND Activity in a Process Flow

This illustration is described in the surrounding text.
Description of "Figure 21-1 AND Activity in a Process Flow"

For outgoing conditions, the AND activity can have one, two, or three conditional transitions. This results in three possible paths terminating in success, warning, and error activities.

Assign

Use the Assign activity to assign a value to a variable. For example, use this activity to initialize a variable back to zero.

This illustration is described in the surrounding text.
Description of the illustration act_icon_assign.gif

Table 21-5 Assign Activity Parameters

Parameter Description

Value

Type in the value to assign to the variable.

Variable

Select a variable that you previously defined in the editor.


Data Auditor

You can design process flows that proceed based on the results of profiling data. For example, you create logic that runs a mapping only if the quality of data meets a standard as determined by the threshold parameter.

This illustration is described in the surrounding text.
Description of the illustration data_auditor_act_icon.gif

Table 21-6 Data Auditor Monitor Activity Parameters

Parameter Description

AUDIT_LEVEL

NONE

STATISTICS

ERROR_DETAILS

COMPLETE

BLUK_SIZE

1+

COMMIT_FREQUENCY

1+

MAX_NO_OF_ERRORS

Maximum number of errors allowed after which the mapping terminates

OPERATING_MODE

SET_BASED

ROW_BASED

ROW_BASED_TARGET_ONLY

SET_BASED_FAIL_OVER_TO_ROW_BASED

SET_BASED_FAIL_OVER_TO_ROW_BASED_TARGET_ONLY


Email

You can send email notifications after the completion of an activity in a process flow. You may find this useful, for example, for notifying administrators when activities such as mappings end in error or warnings.

This illustration is described in the surrounding text.
Description of the illustration email.gif

Table 21-7 lists the parameters you set for the email activity.

Table 21-7 Email Activity Parameters

Parameter Description

SMTP Server

The name of outgoing mail server. The default value is localhost.

Port

The port number for the outgoing mail server. The default value is 25.

From_Address

The email address from which process flow notifications are sent.

Reply_To_Address

The email address or mailing list to which recipients should respond.

To_Address

The email addresses or mailing lists that receive the process flow notification. Use a comma or a semi-colon to separate multiple email addresses.

CC_Address

The email addresses or mailing lists that receive a copy of the process flow notification. Use a comma or a semi-colon to separate multiple email addresses.

BCC_Address

The email addresses or mailing lists that receive a blind copy of the process flow notification. Use a comma or a semi-colon to separate multiple email addresses.

Importance

The level of importance for the notification. You can type in any text, for example, High, Medium, or Low.

Subject

The text that appears in the email subject line.

Message_Body

The text that appears in the body of the email. To type in or paste text, select Value at the bottom of the Activity panel. The Process Flow Editor does not limit you on the amount of text you can enter.


For email addresses, you can enter the email address with or without the display name. For example, the following entries are correct:

jack.emp@example.com

Jack Emp<jack.emp@example.com>

Jack Emp[jack.emp@example.com]

Jack Emp[jack.emp@example.com],Jill Emp[jill.emp@example.com]

Jack Emp[jack.emp@example.com];Jill Emp[jill.emp@example.com]

To execute a process flow with an email activity, you may need to access different host machines and ports. New security measures implemented in Oracle Database 11g Release 1 restrict access to hosts and ports. You must explicitly grant access to hosts and ports that the email activity accesses using the DBMS_NETWORK_ACL_ADMIN package.

For example, the user OWBSYS needs to send an email through the mail server mail.example.com using port 25. The DBA must perform the following steps:

  1. Create an Access Control List (ACL) for the user OWBSYS using the following command:

    EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
            ('acl_for_owb_cc.xml','ACL for Control Center','OWBSYS','CONNECT');
    

    The ACL has no access control effect unless it is assigned to network target.

  2. Assign the Access Control List (ACL) to a network host, and optionally specify a TCP port range. Use the following command:

    EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL
            ('acl_for_owb_cc.xml','mail.example.com',25)
    
  3. Commit the changes made using the COMMIT command.

End

Every path in the process flow must terminate in an End activity.

This illustration is described in the surrounding text.
Description of the illustration end_success.gif

When you first create a process flow, a success type End activity is included by default. Use end types to indicate the type of logic contained in a path. Because a given activity such as a mapping has three possible outcomes, the editor includes three ending types, as shown in Table 21-8. You can use these ending types to design error handling logic for the process flow.

Table 21-8 Types of End Activities

Icon End Type Description
This illustration is described in the surrounding text.

Success

Indicates that the path or paths contain logic dependent on the successful completion of an upstream activity.

This illustration is described in the surrounding text.

Warning

Indicates that the path or paths contain logic dependent on an upstream activity completing with warnings.

This illustration is described in the surrounding text.

Error

Indicates that the path or paths contain logic dependent on an upstream activity completing with errors.


You can design a process flow to include one, two, or all three types of endings. You can use each ending type only once, Duplicate ending types are not allowed. Each End activity can have a single or multiple incoming transitions.

In Figure 21-2, END_SUCCESS has three incoming transitions, each dependent on the successful completion of upstream activities. END_ERROR has one incoming transition from an email activity that runs when any of the upstream mapping activities completes with errors.

Figure 21-2 End Activities in a Process Flow

This illustration is described in the surrounding text.
Description of "Figure 21-2 End Activities in a Process Flow"

By default, every process flow includes an END_SUCCESS. Although you cannot change an end activity to another type, you can add different types of end activity.

To add end activities to a process flow:

  1. From the palette on the Process Flow Editor, drag and drop the desired End icon onto the canvas.

    Warehouse Builder does not allow you to select ending types already present in the process flow.

  2. Click OK.

    Warehouse Builder adds the End activity or activities to the canvas.

End Loop

The editor adds an End Loop for each For Loop and While Loop you add to the canvas.

This illustration is described in the surrounding text.
Description of the illustration end_loop_icon.gif

The End Loop activity must have a single unconditional outgoing transition to its For Loop or While Loop activity. All the flows that are part of the loop must converge on the End Loop activity to ensure that no parallel flows remain for either the next loop interaction or the exit of the loop.

File Exists

Use the File Exists activity to verify the existence of a file before running the next activity. In the Activities panel, type the name of the file.

This illustration is described in the surrounding text.
Description of the illustration file_exists.gif

The File Exists activity checks only once. If the file exists, then the process flow proceeds with the success transition. If the file does not exist, then the process flow proceeds with the warning transition. The File Exists activity triggers the error transition only in the case of a catastrophic failure such as TCL error when using OMB Plus.

The File Exists activity has one parameter called PATH. Specify a fully qualified file name, a directory name, or a semicolon separated list for this parameter. The paths are normally tested in the same host that is running the Control Center service.

The security constraints of the underlying operating system may disallow access to one or more files, giving the impression that they do not exist. If all the paths exist, then the activity returns EXISTS. If none of the paths exist, then the activity returns MISSING. If some paths exist, then the activity returns SOME_EXIST.

FORK

Use the FORK activity to start multiple, concurrent activities after the completion of an activity.

This illustration is described in the surrounding text.
Description of the illustration fork.gif

You can assign multiple incoming transitions to a FORK activity. The FORK activity is the only activity that enables you to assign multiple unconditional outgoing transitions for parallel process. For example, in Figure 21-3, the process flow carry out the activities named FTP, FDS, and EMAIL in parallel after completing MAP1.

Figure 21-3 FORK Activity Ensures Parallel Process

This illustration is described in the surrounding text.
Description of "Figure 21-3 FORK Activity Ensures Parallel Process"

Figure 21-4 shows the same activities without the FORK activity. In this case, only one of the activities runs based on the completion state of MAP1.

Figure 21-4 Absence of FORK Activity Results in Conditional Process

This illustration is described in the surrounding text.
Description of "Figure 21-4 Absence of FORK Activity Results in Conditional Process"

The Process Flow Editor does not limit you on the number of outgoing transitions or concurrent activities you can assign from a FORK. When designing for concurrent execution, design the FORK based on limitations imposed by the workflow engine or server you use to run the process flow.

The outgoing FORK activity transition cannot have complex expressions

For Loop

Use the For Loop to repeatedly run activities you include in the loop and then exit and resume the process flow.

This illustration is described in the surrounding text.
Description of the illustration for_loop.gif

When you add a For Loop, the editor also adds an End Loop activity and a transition to the End Loop. For outgoing transitions, define one with a loop condition and one with an exit condition. Select an outgoing transition and click Condition in the object details.

Table 21-9 For Loop Activity Parameters

Parameter Description

Condition

An expression which when evaluated to true runs the loop transition else the exit transition.

Variable

Bound to a variable or parameter, its value is incremented every iteration.

Initial_Value

The initial value of the variable on entering the loop. By default, you must enter an expression.

Next_Value

The next value of the variable. By default, you must enter an expression


FTP

Use the FTP activity to transfer files from one file location to another based on a script of FTP commands that you provide. The FTP activity is a specialization of the User Defined activity. The difference between these two is that the FTP activity should be configured with the remote file location.

This illustration is described in the surrounding text.

For the process flow to be valid, the FTP commands must involve transferring data either from or to the server with the Control Center Service installed. To move data between two computers, neither of which host the Control Center Service, first transfer the data to the Control Center Service host computer and then transfer the data to the second computer.

Before you design a process flow with an FTP activity, ensure that the sources and destinations have defined locations.

The FTP activity relies on a script of FTP commands that you provide. You have a choice of either writing that script within Warehouse Builder or directing Warehouse Builder to a file containing the script. Choose one of the following methods:

Writing a Script Within Warehouse Builder

Choose this method when you want to maintain the script of FTP commands in Warehouse Builder and/or when password security to servers is a requirement.

For this method, in the COMMAND parameter, enter the path to the FTP executable. Also, for file transfer protocols other than UNIX, type additional parameters for the protocol in the PARAMETER_LIST parameter. Enter a script into the VALUE column of the SCRIPT parameter.

Table 21-10 lists the parameters you set for the FTP activity when writing the script within Warehouse Builder.

Table 21-10 FTP Activity Parameters for Script in Warehouse Builder

Parameter Description

COMMAND

Enter the path to file transfer protocol command such as c:\WINNT\System32\ftp.exe for Windows operating systems.

PARAMETER_LIST

This is a list of parameters that will be passed to the command. Parameters are separated from one another by a token. The token is taken as the first character on the parameter list string, and the string must also end in that token. Warehouse Builder recommends the '?' character, but any character can be used. For example, to pass 'abc,' 'def,' and 'ghi' you can use the following equivalent:

?abc?def?ghi?

or

!abc!def!ghi!

or

|abc|def|ghi|

If the token character or '\' needs to be included as part of the parameter, then it must be preceded with '\'. For example '\\'. If '\' is the token character, then '/' becomes the escape character.

Enter any additional parameters necessary for the file transfer protocol.

For Windows, enter ?"-s:${Task.Input}"? The ${Task.Input} token prompts Warehouse Builder to store the script in temporary file and replaces the token with the name of the temporary file. The script is therefore not passed on as standard input.

Note: The -s parameter is set for the Windows FTP command because it cannot be used with standard input except from a file.

For UNIX, you should leave this value blank. In general, UNIX FTPs read from standard input and therefore do not require any other parameters.

SUCCESS_THERSHOLD

Designates the FTP command completion status.Type in the highest return value from the operating system that indicates a successful completion. When the operating system returns a higher value, it indicates that the command failed.

The default value is 0.

SCRIPT

You can type the required script for FTP in this parameter.

To type or paste text, select Value at the bottom of the Object Details panel. The Process Flow Editor does not limit you on the amount of text you can enter.

Each carriage return in the script is equivalent to pressing the Enter key. The script should end with bye or quit followed by a carriage return to ensure that the FTP command is terminated.


Figure 21-5 shows an example of the FTP activity parameter settings for calling a script written within Warehouse Builder.

Figure 21-5 Activity View for FTP Activity Using a Script

Description of Figure 21-5 follows
Description of "Figure 21-5 Activity View for FTP Activity Using a Script"

Figure 21-6 shows the first line of the script in the VALUE column of the SCRIPT parameter. To view the entire script, click the Colon button to the right of the Value column. Warehouse Builder displays the SCRIPT Value editor that you can use to write or copy and paste a FTP script such as the script displayed in Figure 21-6.

Figure 21-6 SCRIPT Value Editor Using Substitution Variables

Description of Figure 21-6 follows
Description of "Figure 21-6 SCRIPT Value Editor Using Substitution Variables"

Notice that the script inFigure 21-6 includes ${Remote.User} and ${Remote.Password}. These are substitution variables. See "Using Substitution Variables" for more details.

Using Substitution Variables

Substitution variables are available only when you choose to write and store the FTP script in Warehouse Builder.

Use substitution variables to prevent having to update FTP activities when server files, accounts, and passwords change. For example, consider that you create 10 process flows that utilize FTP activities to access a file on salessrv1 under a specific directory. If the file is moved, without the use of substitution variables, you must update each FTP activity individually. With the use of substitution variables, you need only update the location information.

Substitution variables are also important for maintaining password security. When an FTP activity is run with substitution variables for the server passwords, it resolves the variable to the secure password you entered for the associated location.

Table 21-11 lists the substitute variables you can enter for the FTP activity. Working refers to the computer hosting the Control Center Service, the local computer in this case study. Remote refers to the other server involved in the data transfer. You designate which server is remote and local, when you configure the FTP activity as described in "Configuring Process Flows Reference".

Table 21-11 Substitute Variables for the FTP Activity

Variable Value

${Working.RootPath}

The root path value for the location of the Control Center Service host.

${Remote.Host}

The host value for the location involved in transferring data to or from the Control Center Service host.

${Remote.User}

The user value for the location involved in transferring data to or from the Control Center Service host.

${Remote.Password}

The password value for the location involved in transferring data to or from the Control Center Service host.

${Remote.RootPath}

The root path value for the location involved in transferring data to or from the Control Center Service host.

${Task.Input}

The Working and Remote location are set for the FTP activity when configuring a Process Flow.

${parameter_name}

The values of custom parameters can be substituted into the script and parameter using ${parameter_name} syntax.


All custom parameters are imported into the command's environment space. For example, by defining a custom parameter called PATH it is possible to change the search path used to locate OS executables (some JAVA VMs may prevent this).

Calling a Script Outside of Warehouse Builder

If password security is not an issue, you can direct Warehouse Builder to a file containing a script including the FTP commands and the user name and password.

To call a file on the file system, enter the appropriate command in PARAMETERS_LIST to direct Warehouse Builder to the file. For a Windows operating system, enter the following: ?"-s:<file path\file name>"?

For example, to call a file named move.ftp located in a temp directory on the C drive, enter the following: ?"-s:c:\temp\move.ftp"?

Leave the SCRIPT parameter blank for this method.

Table 21-12 lists the parameters you set for the FTP activity when the FTP script resides in a file on your system.

Table 21-12 FTP Activity Parameters for Script Outside of Warehouse Builder

Parameter Description

Command

Leave this parameter blank.

Parameter List

Enter the path and name of the file for the FTP script.The Process Flow Editor interprets the first character you type to be the separator. For example, the Process Flow Editor interprets the following entry as two parameters, /c and dir

?/c?dir?

Use the backslash as the escape character. For example, the Process Flow Editor interprets the following entry as three parameters, -l and -s and /

/-l/-s/\//

Success Threshold

Designates the FTP command completion status.Type in the highest return value from the operating system that indicates a successful completion. When the operating system returns a higher value, it indicates that the command failed.

The default value is 0.

Script

Leave this parameter blank.


Manual

Use the Manual activity to halt a process flow.

This illustration is described in the surrounding text.
Description of the illustration act_icon_manual.gif

Once the process flow halts, a user must intervene via the Control Center or Repository Browser to resume the process flow.

Consider using this activity to enable you to design a process to restart or recover ETL processes.

The manual activity is similar to the Notification activity except that it does not require you to implement Oracle Workflow and therefore does not send an email. To achieve the same results as the notification activity without interacting with Oracle Workflow, consider using the Email activity followed by a manual activity.

Table 21-13 Manual Activity Parameters

Parameter Description

Performer

The name of the person or group that can resume the process flow.

Subject

Type in the subject of the activity.

Text_body

Type in special instructions to be performed before resuming the process flow.

Priority

Select a priority. The options are: 1= high, 50=medium, and 99=low.


Mapping

Use the mapping activity to add an existing mapping that you defined and configured in the Mapping Editor.

This illustration is described in the surrounding text.
Description of the illustration mapping.gif

You can assign multiple incoming transitions to a mapping activity. For outgoing transitions, assign one unconditional transition or up to one of each of the unconditional transitions.

When you add a mapping to a process flow, you can view its configuration properties in the Activities panel. The mapping activity in the Process Flow Editor inherits its properties from the mapping in the Mapping Editor. In the Process Flow Editor, you cannot change a property data type or direction.

You can, however, assign new values that affect the process flow only and do not change the settings for the mapping in the Mapping Editor. For example, if you change the operating mode from set-based to row-based in the Process Flow Editor, the process flow runs in row-based mode. The original mapping retains set-based mode as its operating mode. If you want to change the properties for the underlying mapping, see "Configuring Mappings Reference".

If a mapping contains a Mapping Input Parameter operator, specify a value according to its data type. The Process Flow Editor expects to receive a PL/SQL expression when you add a Mapping Input Parameter operator to a mapping. If the Mapping Input Parameter is a string, enclose the string in double quotes.

If you want to update a process flow with changes you made to a mapping in the Mapping Editor, delete the mapping activity from the process flow and add the mapping activity again.

Table 21-14 and Table 21-15 list the different mapping parameters in PL/SQL and SQL*Loader.

Table 21-14 lists the PL/SQL mapping parameters.

Table 21-14 Mapping parameters for PL/SQL

Parameter Valid Values

AUDIT_LEVEL

NONE

STATISTICS

ERROR_DETAILS

COMPLETE

BLUK_SIZE

1+

COMMIT_FREQUENCY

1+

MAX_NO_OF_ERRORS

Maximum number of errors allowed after which the mappings will terminate with an error

OPERATING_MODE

SET_BASED

ROW_BASED

ROW_BASED_TARGET_ONLY

SET_BASED_FAIL_OVER_TO_ROW_BASED

SET_BASED_FAIL_OVER_TO_ROW_BASED_TARGET_ONLY


Table 21-15 lists the SQL*Loader mapping parameters.

Table 21-15 Mapping parameters for SQL*Loader

Parameter Description

BAD_FILE_NAME

The name of the SQL*Loader "BAD" file

DATA_FILE_NAME

The name of the SQL*Loader "DATA" file

DISCARD_FILE_NAME

The name of the SQL*Loader "DISCARD"file


Notification

This illustration is described in the surrounding text.
Description of the illustration act_icon_notifcation.gif

The Notification activity enables you to design a process to restart or recover ETL processes.This activity works in conjunction with Oracle Workflow. To implement notifications you must also implement Workflow notifications in Oracle Workflow. Alternatively, you could use an Email followed by a Manual activity. Oracle Workflow subsystem decides how the message is sent.

To use the notification activity, first define the parameters listed in Table 21-16. Define a conditional outgoing transition based on each response you define. For example, if the value of response_type is yes, no and default_response is yes, define two outgoing transitions. Right-click each transition and select Condition to view a list of conditions. In this example, you create one outgoing transition with condition set to yes and another set to no.

Table 21-16 Parameters for the Notification Activity

Parameter Description

Performer

Type the name of a role defined by the Oracle Workflow administrator.

Subject

Type in the subject of the email.

Text_body

Type in instructions for the performer. Explain how their response affects the process flow and perhaps explain the default action if they do not respond.

Html_body

Use html in addition to or instead of text. Content you enter in html_body is appended to text_body.

Response_type

Type a comma separated list of values from which the performer selects a response. Each entry corresponds to one outgoing transition from the activity.

Default_response

Type the default response.

Priority

Select a priority for the email of either 1 (high), 50 (medium), or 99 (low).

Timeout

The number of seconds to wait for response. If this is set, a #TIMEOUT transition is required.

Response_processor

Oracle Workflow notification response processor function. For more information, see the Oracle Workflow documentation.

Expand_roles

Used for notification voting. Set this value to TRUE or FALSE. When set to TRUE, a notification is sent to each member of a group rather then a single shared message to the group. For more information, see the Oracle Workflow documentation.


Note:

Due to Oracle Workflow restriction, only the performer, priority, timeout, and customer parameter values can be changed at runtime.

Notification Message Substitution

Custom parameters can be added to the notification activity to pass and retrieve data from the user through the notification. IN parameters can be substituted into the message using SQL and appropriate syntax. For example, for a custom parameter called NAME, the text &NAME will be replaced with the parameter's value. You will also be prompted to enter values for the OUT Parameters.

OR

Use the OR activity to start an activity based on the completion of one or multiple number of upstream activities. You can assign multiple incoming transitions and only one unconditional outgoing transition to an OR activity.

The OR activity has similar semantics to the AND activity except that the OR activity propagates the SUCCESS, WARNING, or ERROR outcome of the first upstream activity that is completed.

This illustration is described in the surrounding text.
Description of the illustration or.gif

An OR activity in a process flow ensures that downstream activities are triggered only once for each run of a process flow.

Figure 21-7 The OR activity in an process flow

This illustration is described in the surrounding text.
Description of "Figure 21-7 The OR activity in an process flow"

The Process Flow Editor enables you to omit the OR activity and assign transitions from each of the three Mapping activities to SUBPROC1 shown in Figure 21-7. However, this logic would start SUBPROC1 three times within the same run of a process flow. Avoid this by using an OR activity.

Route

Use the Route activity to route the outcome of an activity to specific results based on a condition you define. This enables you to define exclusive OR and if-the-else scenarios.

This illustration is described in the surrounding text.
Description of the illustration route.gif

A Route activity has no operation and therefore can be used to place a bend in a transition. Like any other activity, you can add outgoing complex condition transitions to the Route activity. But since the activity has no operation, the condition may only refer to the process flow's parameters and variables.The inclusion of a Route activity can effect the outcome of an AND or OR activity. Since the Route activity has no outcome of its own, it will be considered to have completed as SUCCESS.

This activity does not have any parameters.

Set Status

Use the Set Status activity to interject a success, warning, or error status.

This illustration is described in the surrounding text.
Description of the illustration act_icon_set_status.gif

You can use set status as a means of overriding the behavior of the AND activity. Recall that if any of the activities immediately preceding an AND return an error, the AND activity resolves to an error. If you want the AND to resolve to success regardless of the result of a preceding activity, insert between that activity and the AND activity a set status.

Sqlplus

Use a sqlplus activity to introduce a script into the process flow.

This illustration is described in the surrounding text.
Description of the illustration act_icon_sql_plus.gif

To paste or type in a script, select the activity on the canvas, select Script in the editor explorer, and then click Value in the object details. Or, to point to an existing script on a file system, go to parameter_list and type the at sign, @, followed by the full path.

Although you can use this activity to accomplish a broad range of goals, one example is to use a sqlplus activity to control how multiple mappings are committed in a process flow as described in "Committing Mappings through the Process Flow Editor".

Using Activities in Process Flows

The Process Flow in SQLPlus activity is carried out by the configuration item in the Deployed Location.

To set the location that will run the SQLPlus activity:

  1. In Project Explorer, expand the Process Flow module.

  2. In the Process Flow module, select Process Flow.

  3. Right-click Process Flow and select Configure.

  4. In Configuration Properties window, expand SQL*Plus activity.

  5. Select SQLPLUS.

    Figure 21-8 Configuring the Deployed Location

    Description of Figure 21-8 follows
    Description of "Figure 21-8 Configuring the Deployed Location"

  6. Under Path Settings, set the Deployed Location option to the location that will run the SQLPLUS Activity.

The SQLPlus activity is similar to the User Defined activity with the following differences:

  • The COMMAND parameter cannot be specified as it is automatically derived.

  • If the ${Task.Input} substitution variable is used then the temporary file that is created will end in .sql.

  • It has a different set of substitution variables. The activity should be configured with a Deployed database location.

Table 21-17 SqlPlus Activity Parameters

Parameter Description

Parameter_List

Type @ followed by the full path of the location of the file containing the script.

Script

As a alternative to typing the path in parameter_list, type or paste in a script.


Using Substitution Variables

The substitution variables are similar to FTP. It uses the following location instead of the remote location as it is connecting to an Oracle Database and not a FTP server:

  • Working location as the local location

  • Deployed location as the target location

Table 21-18 SqlPlus Substitution Variables

Substitution Variable Description

${Working.RootPath}

The local working directory

${Task.Input}

A temporary file create from the SCRIPT parameter

${Target.Host}

The target location's host name

${Target.Port}

The target location's post number

${Target.Service}

The target location's service name

${Target.TNS}

The target location's TNS address

${Target.Schema}

The target location's schema name

${Target.User}

The target location's user name

${Target.Password}

The target location's user password

${Target.URL}

The target location's connection descriptor


If the PARAMTER_LIST is empty then one of the following parameter list is used depending on the Deployed location parameters:

  • ?${Target.User}/${Target.Password}@${Target.TNS}?@${Task.Input}?

  • ?${Target.User}/${Target.Password}@${Target. URL}?@${Task.Input}?

  • ?${Target. Schema}/${Target.Password}@${Target.TNS}?@${Task.Input}?

  • ?${Target. Schema}/${Target.Password}@${Target. URL}?@${Task.Input}?

SQL *Plus Command

The SQL*Plus command cannot be entered directly to the FTP User Defined activities. It is either loaded from the home directory or its location is predefined by the workspace administrator.

The Sql*Plus execution location is determined from the following platform properties in the following order:

  1. property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_10g

  2. property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_9i

  3. property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_8i

  4. property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_default

The Oracle home is determined in a similar way from the following platform properties:

  1. property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_10g

  2. property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_9i

  3. property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_8i

  4. property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_default

Start

By default, each process flow includes one start activity. You can set input parameters for the start activity that become the input parameters for the complete process flow.

This illustration is described in the surrounding text.
Description of the illustration start.gif

To add parameters to a Start activity:

  1. In Project Explorer, double-click the Process Flow to open the Process Editor.

  2. In Explorer pane, click Selected Objects tab.

  3. In Selected Objects tab, expand the Activities folder.

  4. Select Start activity and click create button (the tiny green "Plus" button at the left top corner) on the Explorer pane.

    The Process Flow Editor adds a parameter under the Start activity.

    Figure 21-9 Parameter added to a Start activity

    Description of Figure 21-9 follows
    Description of "Figure 21-9 Parameter added to a Start activity"

  5. In Object Details pane, set the properties for the parameter.

    Change the parameter name and data type as necessary. You cannot alter its direction. The direction is IN, indicating that the parameter is an input parameter only. For value, type the parameter value. You can overwrite this value at runtime.

  6. You can now use the parameter as input to other activities in the process flow.

Subprocess

Use a Subprocess activity to start a previously created process flow. From one process flow, you can start any other process flow that is contained within the same or any other process flow package.

This illustration is described in the surrounding text.
Description of the illustration subprocess.gif

Once you add a subprocess to a process flow, use it in your design similar to any other activity. You can assign multiple incoming transitions. For outgoing transitions, assign either one unconditional outgoing transition or up to three outgoing conditional transitions.

The END activities within the subprocess apply to the Subprocess only and do not function as a termination point in the process flow.

An important difference between a subprocess and other activities is that you can view the contents of a subprocess, but you cannot edit its contents in the parent process flow. To edit a subprocess, open its underlying process flow from the Project Explorer. With the exception of renaming a process flow, the Process Flow Editor propagates changes from child process flows to its parent process flows.

Note:

Use caution when renaming process flows. If you rename a process flow referenced by another process flow, the parent process flow becomes invalid. You must delete the invalid subprocess and add a new subprocess associated with the new name for the child process flow.

To add subprocess activities to a process flow:

  1. From the palette in the Process Flow Editor, drag and drop the Subprocess activity icon onto the canvas.

    Warehouse Builder displays a dialog box to select and add a process flow as a subprocess.

  2. Expand the process flow module and select a process flow from the same process flow package as the parent process flow.

    Warehouse Builder displays the process flow as a subprocess activity on the parent process flow.

  3. To view the contents of the subprocess, right-click the subprocess and select Expand Node.

    The Process Flow Editor displays the graph for the subprocess surrounded by a blue border.

Transform

When a function transform is dropped onto the canvas the return parameter is created as a new parameter with the same name as the transform. When you add transformations from the transformation library to a process flow using the Transform activity, the Process Flow Editor displays the parameters for the transformation in the Activity panel.

This illustration is described in the surrounding text.
Description of the illustration transform.gif

You can specify one or more incoming transitions to start a transform activity. For outgoing transitions, you can either specify one unconditional transition or one of each of the three conditional transitions.

If you specify conditional outgoing transitions, you can configure the activity to base its status on its return value. For more information about Use Return as Status, see "Configuring Process Flows Reference".

If you want to update a process flow with changes you made to a transformation, delete the transform activity from the process flow and add the transform activity again.

For transforms that are not deployed, such as the public transformations, the activity must be configured with a Deployed location value.

User Defined

The User Defined activity enables you to incorporate into a process flow an activity not defined within Warehouse Builder.

This illustration is described in the surrounding text.
Description of the illustration external_process.gif

You can specify one or more incoming transitions to start a user defined process activity. For outgoing transitions, you can either specify one unconditional transition or one of each of the three conditional transitions.

If you specify conditional outgoing transitions, you can configure the activity to base its status on its return value. For more information about Use Return as Status, see "Configuring Process Flows Reference".

Table 21-19 lists the parameters you set for the FTP activity.

Table 21-19 User Defined Process Activity Parameters

Parameter Description

Command

The command to carry out the user defined process you defined. Type the path and file name such as c:\winnt\system32\cmd.exe .

Parameter List

The list of parameters to be passed to the user defined process. Type the path and file name such as ?/c?c:\\temp\\run.bat.

The Process Flow Editor interprets the first character you type to be the separator. For example, the Process Flow Editor interprets the following entry as /c and dir.

?/c?dir?

Use the backslash as the escape character. For example, the Process Flow Editor interprets the following entry as -l and -s and /.

/-l/-s/\//

You can also enter the substitution variables listed in Table 21-20.

Success Threshold

Designates the completion status.Type in the highest return value from the operating system that indicates a successful completion. When the operating system returns a higher value, it indicates that the command failed. The default value is 0.

Script

You can enter a script here or type in a file name for a script. If you type in a file name, use the ${Task.Input} variable in the parameter list to pass the file name.

To type in or paste text, select Value at the bottom of the Activity panel. The Process Flow Editor does not limit you on the amount of text you can enter.

Each carriage return in the script is equivalent to pressing the Enter key. Therefore, end the script with a carriage return to ensure that the last line is sent.


Table 21-20 lists the substitute variables you can enter for the FTP activity.

Table 21-20 Substitute Variables for the User Defined Process Activity

Variable Value

${Working.Host}

The host value for the location of the Control Center Service host.

${Working.User}

The user value for the location of the Control Center Service host.

${Working.Password}

The password value for the location of the Control Center Service host.

${Working.RootPath}

The local working directory.

${Task.Input}

A temporary file created from the SCRIPT parameter.

Enter the Task.Input variable to direct Warehouse Builder to the script you write in the SCRIPT parameter.

For Windows, enter into Parameter_List ?"-s:${Task.Input}"?

and for UNIX, enter into Parameter_List ?"${Task.Input}"?

where the question mark as the separator.


Wait

Use the Wait activity to interject a delay in the process flow.

This illustration is described in the surrounding text.
Description of the illustration act_icon_wait.gif

Table 21-21 Wait Activity Parameters

Parameter Description

Minimum_Delay

Type in the minimum time to wait. Specify the time in units of seconds.

Until_Date

Specify the date to wait until in the default format for your local region.


While Loop

Use While Loop to run one or more activities only when a condition you define evaluates to being true.

This illustration is described in the surrounding text.
Description of the illustration while_loop.gif

Typically, you associate a While Loop with Assign activities that enable you to define the while condition. At least one assign activity initializes the data and at least one assign activity increments or modifies the data again to the end of a loop iteration.

When you add a While Loop, the editor also adds an End Loop activity and a transition to the End Loop. Create transitions from the While Loop activity to each activity you want to include in the loop. For each outgoing transition you add, apply either an EXIT or LOOP condition to the transition by selecting the transition and clicking on Condition in the object details.

To define the while condition that governs whether or not to run the loop, select the While Loop activity, select Condition in the editor Explorer, and then define the condition in the object details.

Table 21-22 While Loop Activity Parameters

Parameter Description

Condition

.Define with a LOOP or EXIT condition.