Publishing Process Flow as a Web Service

Here we see how to publish an OWB process flow as a web service using 10g components (11g database has such capabilities built-in). OWB contains a PL/SQL package WB_RT_API_EXEC with a function called RUN_TASK that submits execution requests to the OWB runtime engine. The requests could be for mappings, process flows or scheduled jobs. This example illustrates how the function may be published as a web service to allow the execution of OWB process flows.

It is assumed that the reader is able to create Web Services using JDeveloper. For the purposes of this document; please imagine that a process flow MY_PKG / MY_PF (that's package name / process flow name) has been deployed to process flow location PFMOD_LOCATION1.
 
Overview of the RUN_TASK function

The specification for the function is

  function run_task
  ( p_location_name in varchar2
  , p_task_type in varchar2
  , p_task_name in varchar2
  , p_custom_params in varchar2 default null
  , p_system_params in varchar2 default null
  , p_oem_friendly in number default 0
  , p_background in number default 0
  ) return number;

this is the same function used by the sqlplus_exec_template.sql script in OWB 10gR2. It may be called using (assuming a repository owner connection);

declare
  result_num number;
begin
  result_num:= wb_rt_api_exec.run_task('PFMOD_LOCATION1', 'PROCESS', 'MY_PKG/MY_PF', ',', ',', 0 ,0);
end;

Full documentation of the function can be found in <OWB_ORACLE_HOME>/owb/rtasst/wb_rt_api_exec.pls

Publishing the Web Service using JDeveloper

Use the following steps to publish the function via JDeveloper;
  1. Create a new database connection referring to the OWB repository owner schema.
  2. Run JDeveloper�s �Create PL/SQL Web Service� wizard using the database connection and select package WB_RT_API_EXEC and then program unit FUNCTION RUN_TASK
  3. Build then deploy the WAR file to an Oracle Application Server
It is recommended that the deployed web services be protected using J2EE security techniques. Once available the web service endpoint will look like the following;

wb_rt_api_exec_run_task endpoint - For a formal definition, please review the Service Description (rpc style).

wb_rt_api_exec_run_task service
The following operations are supported.

·         runTaskBSSSSSBB
·         runTaskBSSSSSB
·         runTaskBSSSSS
·         runTask
·         runTaskBSSSS

JDeveloper has generated a selection of web services as the run_task function has default parameters. The runTask web service can be used to submit an execution request for any process flow, or map. In our case, to execute the process flow use the following values;

Process WS 1:

Web Services using Target Schema privileges

The previous example creates a Web Service using an OWB repository owner schema, you don't really want to to this, since it opens up the execution of a lot more objects than what you probably desire. Enterprise solutions should create Web Services using OWB target schema credentials so that end users can only access targets for which they know the password ie. they must have previously registered the location�s password.

In order to do this, you must create the following function in an OWB target user schema and then select it as the web service program unit when publishing as a web service from JDeveloper;

-- With this you will have to change the name of your CC owner, I have used OWB_CC
create or replace function execute_code ( obj_loc  varchar2,
                                          obj_typ  varchar2,
                                          obj_name varchar2,
                                          sys_parm varchar2,
                                          cus_parm varchar2)
                          RETURN NUMBER  AUTHID CURRENT_USER as
  r number;
  cc_owner varchar2(256) := 'OWB_CC';
begin
  execute immediate 'set role OWB_D_'||cc_owner||', OWB_O_' || cc_owner;
  execute immediate 'begin :r := '||cc_owner||'.wb_rt_api_exec.run_task(:obj_loc, :obj_typ, :obj_name, :cus_parm, :sys_parm,0,0); end;' using OUT r, IN obj_loc, IN obj_typ, IN obj_name, IN cus_parm, IN sys_parm;
  return r;
end;

This function has been defined with invoker rights and calls the OWB function with definer rights.

If the �share location password preference� is not set then please make sure that all locations have been registered by the OWB user that is submitting execution requests.

Web Services for specific execution objects

A further refinement is to create a Web Service that submits a specific execution to the OWB runtime engine. The above examples illustrated how to have generic functions to execute any process flow. An example function that can only call the process flow mentioned above is;

-- With this you will have to change the name of your CC owner, I have used OWB_CC
create or replace function execute_my_pf ()
                          RETURN NUMBER  AUTHID CURRENT_USER as
  r number;
  cc_owner varchar2(256) := 'OWB_CC';
begin
  execute immediate 'set role OWB_D_'||cc_owner||', OWB_O_' || cc_owner;
  execute immediate 'begin :r := '||cc_owner||'.wb_rt_api_exec.run_task('PFMOD_LOCATION1', 'PROCESS', 'MY_PKG/MY_PF', ',', ',', 0 ,0); end;' using OUT r, IN obj_loc, IN obj_typ, IN obj_name, IN cus_parm, IN sys_parm;
  return r;
end;

This has some advantages in that you can build a much more specific interface for calling the item and parameters can be defined explicitly rather than via the generic interface which provides greater flexibility for calling any flow/map.

Comments:

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