How to Use USER_DEFINED Activity in OWB Process Flow

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.

snap390

Leave all parameters of activity USER_DEFINED unchanged except that we enter /tmp/test.sh into the Value column of the COMMAND parameter.

snap391

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.

snap392

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:

snap393

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:

snap410

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.

snap408

snap409

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:

snap411

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.

snap396

PARAMETER_LIST: Set it blank.

snap400

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.

snap398

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.

snap401

snap402

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):

snap405

We can set complex condition for the transition from USER_DEFINED to SUB_PROCESS_1 like this:

snap404

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

Comments:

Great stuff!

Posted by antonio on May 20, 2010 at 05:33 AM PDT #

Hi, I tried to call a shell script (filemv2.sh) from OWB 11g R1 process flow(WF 2.6.4). For that i used a USER_DEFINED activity in this. In the COMMAND section in the VALUE part i have given /marc_data/erepos/zbfile/filemv2.sh I can deploy this Process flow but could not execute. It is throwing errors: -------------------------------------------------------------------------------- FILEMV:USER_DEFINED Error java.io.IOException: /marc_data/erepos/zbfile/filemv2.sh: not found FILEMV:USER_DEFINED INFORMATIONAL RPE-02235: The operating system has reported that it cannot execute the requested command. This may be because the command or the parameters are invalid, or that the command is shell command. Any result code return is specific to the operating system. Debug information has been produced. -------------------------------------------------------------------------------- Could anyone help in this regard? Thanks in Advance....

Posted by Boopathy Vasagam on June 16, 2010 at 07:06 AM PDT #

Hi Boopathy , The debug information is complaining about that the shell script is not found. Could you please make sure you have correctly typed the full path?

Posted by Jinggen He on June 17, 2010 at 10:29 AM PDT #

Hi,

it works fine. But when I compare the credentials of the os-user oracle on my system (id oracle):

(uid=2347(oracle) gid=239(oinstall), groups=213(mig),240(dba),213(mig),226(dwh))

with an output of the statement from a testscript executed by OWF

(uid=2347(oracle) gid=239(oinstall), groups=240(dba))

I loose the groups 213 and 216. Any suggestions are welcome.

JC

Posted by guest on January 31, 2012 at 10:39 AM PST #

Hi

I am trying to call Unix script in Process flow using user defined activity and i did and deployed the code. But when i execute the code it has given an error at User defined Activity. saying scripts are deployed.

Could you please advise if we need to deploy unix scripts to Target database.

Thanks

Posted by Raju on March 04, 2013 at 03:56 AM PST #

If you want to execute a shell script, the shell script must be accessible on the machine where the OWB control center service is running. You will also need to enable the control center service to execute such scripts as mentioned above in the Runtime.properties files.

Cheers
David

Posted by David on March 07, 2013 at 08:46 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today