X

Welcome to All Things Warehouse Builder

How to Execute Process Flow from SQL*Plus

Guest Author

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

  1. "user/password@tns_name" gives the credentials to connect to OWB workspace. It can be a workspace owner or a workspace user.
  2. The workspace to run this Process Flow is "MY_WORKSPACE"
  3. "OWF_LOCATION" is the Oracle Workflow Location, into which the Process Flow to be executed has been deployed.
  4. "PROCESS" indicates the object to execute is of type Process Flow.
  5. "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.

snap163

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

snap164

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.

snap162

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"

snap165

And bellows is the output.

snap166

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' 

Join the discussion

Comments ( 2 )
  • SONU Tuesday, July 27, 2010
    I want to list all mapping and table names in a excel sheet. I used MBLUS command but not able to understand that How can I export that result into an excel sheet. Please help me.
  • Michael Reitsma Thursday, February 14, 2013

    Another very easy to ease is this one:

    http://docs.oracle.com/cd/E11882_01/owb.112/e10935/scheduling_etl.htm#CIHJBCCA

    Which describes the use of WB_RT_API_EXEC.RUN_TASK function.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.