Process Flow is a very important component of Oracle Warehouse Builder. With Process Flow, we can create and control
the ETL process by setting all kinds of activities in a well-constructed flow. In Oracle Warehouse Builder 11gR2, there are 28 kinds of activities, which fall into three categories: Control activities, OWB specific activities and Utility activities. For more information about Process Flow activities, please refer to OWB online doc. Most of those activities are pre-defined for some specific use. For example,
the Mapping activity allows execution an OWB mapping in Process Flow and
the FTP activity allows an interaction between
the local host and a remote FTP server. Besides those activities for specific purposes,
the User Defined activity enables you to incorporate into a Process Flow an activity that is not defined within Warehouse Builder. So
the User Defined activity brings flexibility and extensibility to Process Flow. In this article, we will take an amazing tour of using
the User Defined activity. Let's start. Enable execution of User Defined activity Let's start this section from creating a very simple Process Flow, which contains a Start activity, a User Defined activity and an End Success activity. Leave all parameters of activity USER_DEFINED unchanged except that we enter /tmp/test.sh into
the Value column of
the COMMAND parameter. Then let's create
the shell script test.sh in /tmp directory. Here is
the content of /tmp/test.sh (this article is demonstrating a scenario in Linux system, and /tmp/test.sh is a Bash shell script): echo Hello World! > /tmp/test.txt Note: don't forget to grant
the execution privilege on /tmp/test.sh to OS Oracle user. For simplicity, we just use
the following command. chmod +x /tmp/test.sh OK, it's so simple that we’ve almost done it. Now deploy
the Process Flow and run it. For a newly installed OWB, we will come across an error saying "RPE-02248: For security reasons, activity operator Shell has been disabled by
the DBA". See below. That's because, by default,
the User Defined activity is DISABLED. Configuration about this can be found in <ORACLE_HOME>/owb/bin/admin/Runtime.properties: property.RuntimePlatform.0.NativeExecution.Shell.security_constraint=DISABLED
The property can be set to three different values: NATIVE_JAVA, SCHEDULER and DISBALED. Where NATIVE_JAVA uses
the Java 'Runtime.exec' interface, SCHEDULER uses a DBMS Scheduler external job submitted by
the Control Center repository owner which is executed by
the default operating system user configured by
the DBA. DISABLED prevents execution via these operators. We enable
the execution of User Defined activity by setting: property.RuntimePlatform.0.NativeExecution.Shell.security_constraint= NATIVE_JAVA Restart
the Control Center service for
the change of setting to take effect. cd <ORACLE_HOME>/owb/rtp/sql sqlplus OWBSYS/<password of OWBSYS> @stop_service.sql sqlplus OWBSYS/<password of OWBSYS> @start_service.sql And then run
the Process Flow again. We will see that
the Process Flow completes successfully.
The execution of /tmp/test.sh successfully generated a file /tmp/test.txt, containing
the line Hello World!. Pass parameters to User Defined Activity
The Process Flow created in
the above section has a drawback:
the User Defined activity doesn't accept any information from OWB nor does it give any meaningful results back to OWB. That's to say, it lacks interaction. Maybe, sometimes such a Process Flow can fulfill
the business requirement. But for most of
the time, we need to get
the User Defined activity executed according to some information prior to that step. In this section, we will see how to pass parameters to
the User Defined activity and pass them into
the to-be-executed shell script. First, let's see how to pass parameters to
the script.
The User Defined activity has an input parameter named 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. Let's configure
the PARAMETER_LIST parameter as below: And modify
the shell script /tmp/test.sh as below: echo $1 is saying hello to $2! > /tmp/test.txt Re-deploy
the Process Flow and run it. We will see that
the generated /tmp/test.txt contains
the following line: Bob is saying hello to Alice! In
the example
above,
the parameters passed into
the shell script are static. This case is not so useful because: instead of passing parameters, we can directly write
the value of
the parameters in
the shell script. To make
the case more meaningful, we can pass two dynamic parameters, that are obtained from
the previous activity, to
the shell script. Prepare
the Process Flow as below:
The Mapping activity MAPPING_1 has two output parameters: FROM_USER, TO_USER.
The User Defined activity has two input parameters: FROM_USER, TO_USER. All
the four parameters are of String type. Additionally,
the Process Flow has two string variables: VARIABLE_FOR_FROM_USER, VARIABLE_FOR_TO_USER. Through VARIABLE_FOR_FROM_USER,
the input parameter FROM_USER of USER_DEFINED gets value from output parameter FROM_USER of MAPPING_1. We achieve this by binding both parameters to VARIABLE_FOR_FROM_USER. See
the two figures below. In
the same way, through VARIABLE_FOR_TO_USER,
the input parameter TO_USER of USER_DEFINED gets value from output parameter TO_USER of MAPPING_1. Also, we need to change
the PARAMETER_LIST of
the User Defined activity like below: Now,
the shell script is getting input from
the Mapping activity dynamically. Deploy
the Process Flow and all of its necessary dependees then run
the Process Flow. We see that
the generated /tmp/test.txt contains
the following line: USER B is saying hello to USER A! 'USER B' and 'USER A' are two outputs of
the Mapping execution. Write
the shell script within Oracle Warehouse Builder In
the previous section,
the shell script is located in
the /tmp directory. But sometimes, when
the shell script is small, or for
the sake of maintaining consistency, you may want to keep
the shell script inside Oracle Warehouse Builder. We can achieve this by configuring these three parameters of a User Defined activity properly: COMMAND: Set
the path of interpreter, by which
the shell script will be interpreted. PARAMETER_LIST: Set it blank. SCRIPT: Enter
the shell script content. Note that in Linux
the shell script content is passed into
the interpreter as standard input at runtime. About how to actually pass parameters to
the shell script, we can utilize variable substitutions. As in
the following figure, ${FROM_USER} will be replaced by
the value of
the FROM_USER input parameter of
the User Defined activity. So will
the ${TO_USER} symbol. Besides
the custom substitution variables, OWB also provide some system pre-defined substitution variables. You can refer to
the online document for that. Deploy
the Process Flow and run it. We see that
the generated /tmp/test.txt contains
the following line: USER B is saying hello to USER A! Leverage
the return value of User Defined activity All of
the previous sections are connecting
the User Defined activity to END_SUCCESS with an unconditional transition. But what should we do if we want different subsequent activities for different shell script execution results? 1.
The simplest way is to add three simple-conditioned out-going transitions for
the User Defined activity just like
the figure below. In
the figure, to simplify
the scenario, we connect
the User Defined activity to three End activities. Basically, if
the shell script ends successfully,
the whole Process Flow will end at END_SUCCESS, otherwise,
the whole Process Flow will end at END_ERROR (in our case, ending at END_WARNING seldom happens). In
the real world, we can add more complex and meaningful subsequent business logic. 2. Or we can utilize complex conditions to work with different results of
the User Defined activity. Previously, in our script, we only have this line: echo ${FROM_USER} is saying hello to ${TO_USER}! > /tmp/test.txt We can add more logic in it and return different values accordingly. echo ${FROM_USER} is saying hello to ${TO_USER}! > /tmp/test.txt if CONDITION_1 ; then ...... exit 0 fi if CONDITION_2 ; then ...... exit 2 fi if CONDITION_3 ; then ...... exit 3 fi After that we can leverage
the result by checking RESULT_CODE in condition expression of those out-going transitions. Let's suppose that we have
the Process Flow as
the following graph (SUB_PROCESS_n stands for more different further processes): We can set complex condition for
the transition from USER_DEFINED to SUB_PROCESS_1 like this: Other transitions can be set in
the same way. Note that, in our shell script, we return 0, 2 and 3, but not 1. As in Linux system, if
the shell script comes across a system error like IO error,
the return value will be 1. We can explicitly handle such a return value. Summary Let's summarize what has been discussed in this article: How to create a Process Flow with a User Defined activity in it How to pass parameters from
the prior activity to
the User Defined activity and finally into
the shell script How to write
the shell script within Oracle Warehouse Builder How to do variable substitutions How to let
the User Defined activity return different values and in what way can we leverage
