Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
After you design mappings that define the operations for moving data from sources to targets, you can create and define process flows. Use process flows to interrelate mappings and activities external to Warehouse Builder such as email, FTP commands, and operating system executables.
Use this section as a reference for all the activities. The section begins by categorizing activities by type. For detailed descriptions of each activity, see the alphabetical listing in the remainder of this section.
For each of the Oracle Warehouse Builder specific activity as listed in Table 22-1, 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 22-1 Warehouse Builder Specific Activities
Icon | Activity | Brief Description |
---|---|---|
Adds to the process flow an existing data auditor monitor used in data profiling. |
||
Adds an existing mapping to the process flow. |
||
Embeds an existing process flow within the process flow. |
||
Adds an existing transformation to the process flow. |
Committing Data
When you add Warehouse Builder Specific activities to a process flow, the process flow evaluates each of these activities as separate transactions. 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 Using the Process Flow Editor".
Table 22-2 lists each utility activity and shows the associated icon.
Table 22-2 Utility Activities
Icon | Activity | Brief Description |
---|---|---|
Assigns a value to a variable. |
||
Sends an email. For example, send an email message about the status of activities in the process flow. |
||
Use the File Exists activity to check if a file is located on a specified drive or directory. |
||
Launches a file transfer protocol command during a process flow. For example, use the FTP activity to move data files to the machine where a mapping executes. |
||
Halts a process flow and requires manual intervention to resume the process flow. |
||
Can send an email to a user and allows the user to select from a list of responses that dictates how the process flow proceeds. |
||
Interjects a success, warning, or error status. |
||
Runs a SQL*Plus script in a process flow. |
||
Represents an activity not defined by Warehouse Builder and enables you to incorporate it into a process flow. |
||
Delays the progress of the process flow by a specified amount of time. |
Table 22-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 22-3 Control Activities
Icon | Activity | Brief Description | Incoming Transitions | Outgoing Transitions |
---|---|---|---|---|
Specifies the completion of all incoming activities before launching another activity. |
2 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. |
||
End (successfully) |
Designates a path as being successful. |
1 or more allowed |
not allowed |
|
End (with errors) |
Designates a path as ending in errors. |
1 or more allowed |
not allowed |
|
End (with warnings) |
Designates a path as ending with warnings. |
1 or more allowed |
not allowed |
|
Defines the end of a For Loop or While Loop |
1 or more allowed |
1 to For Loop or While Loop only |
||
Use this activity with an End Loop to define constructs that repeat. |
1 from End Loop required plus more from other activities |
1 Loop condition and 1 Exit required |
||
Launches two or more activities after completing an activity. |
1 or more allowed |
2 or more unconditional transitions only |
||
Launches an activity after the completion of any of two or more specified activities. |
2 or more allowed |
1 unconditional transition only |
||
Defines exclusive OR and if-then-else scenarios. |
||||
Executes other activities while a condition is true. |
1 from End Loop required plus more from other activities |
1 Loop condition and 1 Exit required |
Use the AND activity to specify the completion of two or more activities before resuming the process flow.
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 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 activities return 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 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 22-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 3 outgoing transitions while AND_ACTIVITY has 2 incoming transitions. The process flow would also be valid if the transition and activities associated with MAP3 were deleted.
Figure 22-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.
Assigns a value to a variable. For example, use this activity to initialize a variable back to zero.
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.
Table 22-5 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 |
You can instruct Warehouse Builder to 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.
Table 22-6 lists the parameters you set for the email activity.
Table 22-6 Email Activity Parameters
Parameter | Description |
---|---|
SMTP Server |
The name of outgoing mail server. The default value is |
Port |
The port number for the outgoing mail server. The default value is |
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 address(es) or mailing list(s) that receive the process flow notification. Use a comma or a semi-colon to separate multiple email addresses. |
CC_Address |
The email address(es) or mailing list(s) 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 address(es) or mailing list(s) 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 such as, for example, |
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@oracle.com
Jack Emp<jack.emp@oracle.com>
Jack Emp[jack.emp@oracle.com]
Jack Emp[jack.emp@oracle.com],Jill Emp[jill.emp@oracle.com]
Jack Emp[jack.emp@oracle.com];Jill Emp[jill.emp@oracle.com]
Every path in the process flow must terminate in an End activity.
When you first create a process flow, Warehouse Builder includes a success type End activity by default. Use end types to indicate the type of logic contained in a path. Since a given activity such as a mapping has three possible outcomes, the editor includes three ending types, as shown in Table 22-7. You can use these ending types to design error handling logic for the process flow.
Table 22-7 Types of End Activities
Icon | End Type | Description |
---|---|---|
Success |
Indicates that the path or paths contain logic dependent upon the successful completion of an upstream activity. |
|
Warning |
Indicates that the path or paths contain logic dependent upon an upstream activity completing with warnings. |
|
Error |
Indicates that the path or paths contain logic dependent upon 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 22-2, END_SUCCESS has three incoming transitions, each dependent upon 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 22-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 others of different types.
To add end activities to a process flow:
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.
Select OK.
Warehouse Builder adds the End activity or activities to the canvas.
The editor adds an End Loop for each for loop and while loop you add to the canvas.
The End Loop activity must have a single unconditional outgoing transition to its For Loop or While Loop activity. All the flows that for 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.
Use the File Exists activity to verify the existence of a file before executing the next activity. In the Activities panel, type the name of the file.
The File Exists activity checks to see if the file exists only once. If the file exists, the process flow proceeds with the success transition. If the file does not exist, the process flow precedes 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 either a fully qualified filename, directory name, or a semi-colon 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 return EXISTS. If none of the paths exist, then it returns MISSING. If some paths exist, it returns SOME_EXIST.
Use the FORK activity to launch multiple, concurrent activities after the completion of an activity.
You can assign multiple incoming transitions to a FORK. The FORK is the only activity that enables you to assign multiple unconditional outgoing transitions for parallel execution. For example, in Figure 22-3, the process flow executes the activities named FTP, FDS, and EMAIL in parallel after completing MAP1.
Figure 22-3 FORK Activity Ensures Parallel Execution
Figure 22-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 22-4 Absence of FORK Activity Results in Conditional Execution
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
Use the For Loop to repeatedly execute activities you include in the loop and then exit and resume the process flow.
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 on Condition in the object details.
Table 22-8 For Loop Activity Parameters
Parameter | Description |
---|---|
Condition |
An expression which when evaluated to true executes the loop transition other the exit transition. |
Variable |
Bound to a variable or parameter, its value is incremented every iteration. |
Initial_Value |
The initial value of the variable upon 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 |
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.
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 machines, neither of which host the Control Center Service, first transfer the data to the Control Center Service host machine and then transfer the data to the second machine.
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:
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 22-9 lists the parameters you set for the FTP activity when writing the script within Warehouse Builder.
Table 22-9 FTP Activity Parameters for Script in Warehouse Builder
Parameter | Description |
---|---|
COMMAND |
Enter the path to file transfer protocol command such as |
PARAMETER_LIST |
This is a list of parameter 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 escaped 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 past on as standard input. Note: The -s parameter is set for the Windows FTP command because it cannot driven 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 |
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 |
Figure 22-5 shows an example of the FTP activity parameter settings for calling a script written within Warehouse Builder.
Figure 22-5 Activity View for FTP Activity Using a Script
Figure 22-6 shows the first line of the script in the VALUE column of the SCRIPT parameter. To view the entire script, click the : button. 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 22-6.
Figure 22-6 SCRIPT Value Editor Using Substitution Variables
Notice that the script inFigure 22-6 includes ${Remote.User} and ${Remote.Password}. These are substitution variables. Refer to "Using Substitution Variables" for more details.
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 Warehouse Builder executes an FTP activity with substitution variables for the server passwords, it resolves the variable to the secure password you entered for the associated location.
Table 22-10 lists the substitute variables you can enter for the FTP activity. Working refers to the machine hosting the Control Center Service, the local machine 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".
Table 22-10 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 Operating System executables (some JAVA VMs may prevent this).
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 22-11 lists the parameters you set for the FTP activity when the FTP script resides in a file on your system.
Table 22-11 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,
Use the backslash as the escape character. For example, the Process Flow Editor interprets the following entry as three parameters, /-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 |
Script |
Leave this parameter blank. |
Use this activity to halt a process flow.
Once the process flow halts, a user must intervene using 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 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 22-12 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 |
Choose a priority. The options are: 1= high, 50=medium, and 99=low. |
Use the mapping activity to add an existing mapping that you defined and configured in the Mapping Editor.
You can assign multiple incoming transitions to a mapping activity. For outgoing transitions, assign 1 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 executes in row-based mode. The original mapping retains set-based mode as its operating mode. If 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 22-13 and Table 22-14 list the different mapping parameters in PL/SQL and SQL*Loader.
Table 22-13 lists the PL/SQL mapping parameters.
Table 22-13 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 22-14 lists the SQL*Loader mapping parameters.
This activity enables you to design a process to restart or recover ETL processes.
This activity works in conjunction with Oracle Workflow. To implement notifications in Warehouse Builder 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 22-15. 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 22-15 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.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.
Use the OR activity to launch an activity based on the completion of one of a 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.
An OR activity in a process flow ensures that downstream activities are triggered only once for each run of a process flow. For example, in Figure 22-7, the SUBPROC1 launches once any one of the three upstream Mapping activities completes.
Figure 22-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 22-7. However, this logic would launch SUBPROC1 three times within the same run of a process flow. Avoid this by using an OR activity.
Use this 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.
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.
Use this activity to interject a success, warning, or error status.
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.
Use a sqlplus activity to introduce a script into the process flow.
To paste or type in a script, select the activity on the canvas, select Script in the editor explorer, and then click on 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 Using the Process Flow Editor".
The Process Flow in SQLPlus activity is executed by the configuration item in the Deployed Location.
To set the location that will execute the SQLPlus activity:
In Project Explorer, expand the Process Flow module.
In the Process Flow module, select Process Flow.
Right-click Process Flow and select Configure.
In Configuration Properties window, expand SQL*Plus activity.
Select SQLPLUS.
Figure 22-8 Configuring the Deployed Location
Under Path Settings, set the Deployed Location option to the location that will execute 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.
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 22-17 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}?
The SQL*Plus command cannot be entered directly to the FTP User Defined activities. It is either loaded from the Warehouse Builder home or its location is predefined by the repository administrator.
The Sql*Plus execution location is determined from the following platform properties in the following order:
property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_10g
property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_9i
property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_8i
property.RuntimePlatform.0.NativeExecution.SQLPlus.sqlplus_exe_default
The Oracle home is determined in a similar way from the following platform properties:
property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_10g
property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_9i
property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_8i
property.RuntimePlatform.0.NativeExecution.SQLPlus.oracle_home_default
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.
To add parameters to a Start activity:
In Project Explorer, double-click the Process Flow to open the Process Editor.
In Explorer pane, click Selected Objects tab.
In Selected Objects tab, expand the Activities folder.
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 22-9 Parameter added to a Start activity
In Object Details pane, set the properties for the parameter.
Change the parameter name and data type as necessary. You cannot alter its direction and binding. 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.
You can now use the parameter as input to other activities in the process flow.
Use a subprocess activity to launch a previously created process flow. From one process flow, you can launch any other process flow that is contained within the same process flow package.
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 1 unconditional outgoing transition or up to 3 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:
From the palette in the Process Flow Editor, drag and drop the Subprocess activity icon onto the canvas.
Warehouse Builder displays a dialog to select and add a process flow as a subprocess.
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.
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.
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 Warehouse Builder transformation library to a process flow using the Transform activity, the Process Flow Editor displays the parameters for the transformation in the Activity panel.
You can specify one or more incoming transitions to launch 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 on Use Return as Status, see "Configuring Process Flows".
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.
The user defined activity enables you to incorporate into a process flow an activity not defined within Warehouse Builder.
You can specify one or more incoming transitions to launch 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 it return value. For more information on Use Return as Status, see "Configuring Process Flows".
Table 22-18 lists the parameters you set for the FTP activity.
Table 22-18 User Defined Process Activity Parameters
Parameter | Description |
---|---|
Command |
The command to execute the user defined process you defined. Type the path and file name such as |
Parameter List |
The list of parameters to be passed to the user defined process. Type the path and file name such as 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
Use the backslash as the escape character. For example, the Process Flow Editor interprets the following entry as /-l/-s/\// You can also enter the substitution variables listed in Table 22-19. |
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 |
Script |
You can enter a script here or type in a filename for a script. If you type in a filename, use the ${Task.Input} variable in the parameter list to pass the filename. 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 |
Table 22-19 lists the substitute variables you can enter for the FTP activity.
Table 22-19 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. |
Use the wait activity to interject a delay in the process flow.
Use While Loop to execute one or more activities only when a condition you define evaluates to being true.
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.