How to Execute Process Flow from SQL*Plus

By Jinggen He on Dec 02, 2009

As we all know, Process Flow is a component of Oracle Warehouse Builder, which allows activities to be linked together for execution. Using Process flow, we can execute mappings, transformations or external commands such as email and FTP in a well-constructed process.

OWB provides several ways to launch a Process Flow (see links in References):

1. Directly start a Process Flow from Design Center/Control Center.
2. In Design Center, schedule a Process Flow to run at a specified time or to run at regular intervals.
3. Use OMBSTART to start a Process Flow in OMB*Plus command line.

Besides, we also have another way: execute a Process Flow from SQL*Plus. We can do so by utilizing the utility SQL script: <ORACLE_HOME>/owb/rtp/sql/sqlplus_exec_template.sql.

The syntax of sqlplus_exec_template.sql is as follows:

SYNOPSYS @sqlplus_exec_template.sql workspace location_name {PLSQLMAP | SQLLOADERCONTROLFILE | PROCESSFLOW | ABAPFILE | DATAAUDITOR | SCHEDULEDJOB | CTMAPPING} [parent]/task_name system_params custom_params USAGE workspace     := e.g. MY_WORKSPACE    - Workspace in which the job is to run location_name :- e.g. MY_WAREHOUSE    - Physical Name of the Location to which this                                         task was deployed (i.e. a DB Location or a                                         Process Location or the Platform Schema)                                         Note: Always use "PlaformSchema" for                                         SQL_LOADER and SAP types. task_type     :- PLSQLMAP             - OWB PL/SQL Mapping               |  SQLLOADERCONTROLFILE - OWB SQL*Loader Mapping               |  PROCESSFLOW          - OWB ProcessFlow               |  ABAPFILE             - OWB SAP Mapping               |  DATAAUDITOR          - OWB DataAuditor Mapping               |  SCHEDULEDJOB         - OWB Scheduled Job               |  CTMAPPING            - OWB Template Mapping task_name     :- e.g. MY_MAPPING      - Physical Name of the Deployed Object. This                                         can be optionally qualified by the name of a                                         deployed parent, such as the Processflow                                         Package name of a Processflow. A module name                                         cannot be used here because it is not                                         a deployable object. system_params :- { , | (name = value [, name = value]...)}                  e.g. ","                  or   MY_PARAM=1,YOUR_PARAM=true custom_params :- { , | (name = value [, name = value]...)}                  e.g. ","                  or   MY_PARAM=1,YOUR_PARAM=true

The more detailed syntax can be found at the header of sqlplus_exec_template.sql. Actually, as you can see, sqlplus_exec_template.sql is designed for executing several kinds of objects such as PL/SQL Mapping, Code Template Mapping, Process Flow, etc.. Here in this article, we focus only on Process Flow execution. Below is an example of how to execute a Process Flow using the script.

Example sqlplus user/password@tns_name @sqlplus_exec_template.sql MY_WORKSPACE OWF_LOCATION PROCESS PFPKG/PF1 "," "," Explanation "user/password@tns_name" gives the credentials to connect to OWB workspace. It can be a workspace owner or a workspace user. The workspace to run this Process Flow is "MY_WORKSPACE" "OWF_LOCATION" is the Oracle Workflow Location, into which the Process Flow to be executed has been deployed. "PROCESS" indicates the object to execute is of type Process Flow. "PFPKG/PF1" means we are executing a Process Flow named "PF1", which is under the Process Flow Package named "PFPKG".

Let's go through a full example.

First, let's prepare a simple Process Flow containing only one activity: a user-defined activity, which executes a local shell script that takes a parameter from the caller.

Prepare the shell script

In our case, we let the user-defined activity pass a custom Process Flow scope parameter to this shell script. And here is the content of the Shell script to execute.

#!/bin/bash WHO="$1" echo "Process Flow Executed by $WHO" >> /tmp/pf_demo.txt

We put the Shell script in /tmp/pf_demo.sh and add execute privilege for all users. For each execution, pf_demo.sh will add a line to /tmp/pf_demo.txt, reading "Process Flow Executed by Somebody".

Prepare the Process Flow

We use the OMB script in Appendix A to create and deploy the Process Flow: 'PF_DEMO_1/PFM_1/PFPKG_1/PF_1'. You can see that the Process Flow has a custom parameter named " PARAM_WHO" and the user-defined activity passes this parameter to the shell script to execute.

Execute the Process Flow from workspace owner

Then we can execute the command as below:

sqlplus workspace_owner/passwd @sqlplus_exec_template.sql workspace1 PFM_LOCATION PROCESSFLOW "PFPKG_1/PF_1" "," "PARAM_WHO=Owner"

Note that, in Appendix A we have deployed the Process Flow Package "PFPKG_1", which contains a Process Flow "PF_1", to a Oracle Workflow Location "PFM_LOCATION". And in the above example, we pass value "Owner" to parameter "PARAM_WHO".

Here goes the snapshot.

And let's see the output of the shell script.

Execute the Process Flow from workspace user

Also we can use workspace user credentials to start the Process Flow. But before that we should make sure that in "Security Parameters" configuration page (navigated from menu in OWB client "Tools->Preferences"), the item "Share Location Password During Run Time" is checked.

Then we can use workspace user credentials to start the Process Flow as below:

sqlplus workspace_user/passwd @sqlplus_exec_template.sql workspace_owner.workspace1 PFM_LOCATION PROCESSFLOW "PFPKG_1/PF_1" "," "PARAM_WHO=User"

And bellows is the output.

There's a newly added line "Process Flow Executed by User".

Some Important notes

1.   When executing sqlplus_exec_template.sql, we need to specify WORKSPACE, which should be declared as workspaceOwner.workspaceName (if only workspaceName is given, workspaceOwner will be defaulted to user). So if we login as workspace user, we need to specify the workspace owner name.

2.   If you want to pass some parameters to a Process Flow, you can utilize custom_params. It's comma separated key-value pairs string like "PARAM1=1,PARAM1=true" and "," stands for no parameter. In our demo, we passed value "Owner" or "User" to the custom Parameter "PARAM_WHO".

3.   If "Share Location Password During Run Time" is not checked, we have another method to execute the Process Flow from workspace user. That is to login as workspace user and register the OWF location. See OMB commands below.

OMBCONN workspace_user/passwd@localhost:1521:ora111.us.oracle.com OMBCC 'PF_DEMO_1' OMBALTER LOCATION 'PFM_LOCATION' SET PROPERTIES (PASSWORD) VALUES ('owf_mgr') OMBCOMMIT OMBCONN CONTROL_CENTER OMBREGISTER LOCATION 'PFM_LOCATION'

4.   A Process Flow may contain lots of activities such as mappings, transformations. To guarantee that the Process Flow can be executed successfully, you should confirm that all the depended objects have also been deployed correctly.

References

Charpters in Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide 11g Release 2 (11.2)

1. Designing Process Flows
2. Scheduling ETL Jobs

Command syntax in Oracle® Warehouse Builder OMB*Plus Command Reference 11g Release 2 (11.2)

OMBSTART 

Appendix A

The OMB script to create and deploy the demo Process Flow: 

OMBCONN workspace_owner/passwd@localhost:1521:ora111.us.oracle.com OMBCREATE PROJECT 'PF_DEMO_1' OMBCC 'PF_DEMO_1' OMBCREATE LOCATION 'PFM_LOCATION' SET PROPERTIES (TYPE, VERSION) VALUES ('Oracle Workflow','2.6.4') OMBALTER LOCATION 'PFM_LOCATION' SET PROPERTIES (HOST, PORT, SERVICE_NAME, SCHEMA, PASSWORD) VALUES ('localhost', '1521', 'ora111.us.oracle.com', 'owf_mgr', 'owf_mgr') OMBCREATE PROCESS_FLOW_MODULE 'PFM_1' SET REF LOCATION 'PFM_LOCATION' OMBCC 'PFM_1' OMBCREATE PROCESS_FLOW_PACKAGE 'PFPKG_1' OMBCC 'PFPKG_1' OMBCREATE PROCESS_FLOW 'PF_1' OMBALTER PROCESS_FLOW 'PF_1' ADD USER_DEFINED ACTIVITY 'USER_DEFINED' OMBALTER PROCESS_FLOW 'PF_1' MODIFY PARAMETER 'COMMAND' OF ACTIVITY 'USER_DEFINED' SET PROPERTIES (VALUE) VALUES ('/tmp/pf_demo.sh') OMBALTER PROCESS_FLOW 'PF_1' ADD PARAMETER 'PARAM_WHO' SET PROPERTIES (DIRECTION,VALUE) VALUES ('IN','NOBODY') OMBALTER PROCESS_FLOW 'PF_1' ADD PARAMETER 'WHO' OF ACTIVITY 'USER_DEFINED' SET PROPERTIES (BINDING) VALUES ('PF_1.PARAM_WHO') OMBALTER PROCESS_FLOW 'PF_1' MODIFY PARAMETER 'PARAMETER_LIST' OF ACTIVITY 'USER_DEFINED' SET PROPERTIES (VALUE) VALUES ('?\${WHO}?') OMBALTER PROCESS_FLOW 'PF_1' ADD TRANSITION 'TRANSITION_1' FROM ACTIVITY 'START' TO 'USER_DEFINED' OMBALTER PROCESS_FLOW 'PF_1' ADD TRANSITION 'TRANSITION_2' FROM ACTIVITY 'USER_DEFINED' TO 'END' OMBCONN CONTROL_CENTER OMBCOMMIT OMBREGISTER USER 'owf_mgr' OMBCOMMIT OMBREGISTER LOCATION 'PFM_LOCATION' OMBCC '../' OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN 'PLAN_1' OMBALTER DEPLOYMENT_ACTION_PLAN 'PLAN_1' ADD ACTION 'ACTION_1' SET PROPERTIES (OPERATION) VALUES ('CREATE') SET REFERENCE PROCESS_FLOW_PACKAGE 'PFPKG_1' OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'PLAN_1'