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;
, 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;
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;
- Create a new database connection referring to the OWB repository owner schema.
- 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
- Build then deploy the WAR file to an Oracle Application Server
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.
wb_rt_api_exec_run_task service
The following operations are supported.
· runTaskBSSSSSBB
· runTaskBSSSSSB
· runTaskBSSSSS
· runTask
· runTaskBSSSS
· 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;
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;
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;
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.
