Scheduling an OWB 9.0.4/9.2/10.1 Mapping or Process Flow Using DBMS_JOB
To schedule an OWB mapping or process flow to run at a set time, and optionally to run again after a set interval, without having to deploy it to OEM:
First, log on as the Runtime Repository owner, and run the following script to create a function called RUN_OWB_JOB. This is a slightly modified version of the RUN_MY_OWB_STUFF.SQL script downloadable from OTN at http://www.oracle.com/technology/sample_code/products/warehouse/files/run_my_owb_stuff.sql
create or replace function run_owb_job
( p_repos_owner in varchar2 default null
, p_location_name in varchar2 default null
, p_task_type in varchar2 default null
, p_task_name in varchar2 default null
, p_system_params in varchar2 default '","'
, p_custom_params in varchar2 default '","'
, p_oem_friendly in number default 0
) return number
is
l_oem_style boolean := case (p_oem_friendly)
when 0 then false
else true
end ;
l_audit_execution_id number; -- Audit Execution Id
l_audit_result number := wb_rt_api_exec.RESULT_FAILURE; -- Result Code
l_audit_result_disp varchar2(64) := 'FAILURE'; -- Result Display Code
l_task_type_name varchar2(64); -- Task Type Name
l_task_type varchar2(64); -- Task Type
l_task_name varchar2(64); -- Task Name
l_location_name varchar2(64); -- Location Name
l_return number ;
procedure override_input_parameter
(
p_audit_execution_id in number,
p_parameter_name in varchar2,
p_value in varchar2,
p_parameter_kind in number
)
is
l_parameter_kind varchar2(64);
begin
if p_parameter_kind = wb_rt_api_exec.PARAMETER_KIND_SYSTEM
then
l_parameter_kind := 'SYSTEM';
else
l_parameter_kind := 'CUSTOM';
end if;
dbms_output.put_line('| ' || p_parameter_name || '%' || l_parameter_kind || '=' || '''' || p_value || '''');
wb_rt_api_exec.override_input_parameter
(
p_audit_execution_id,
p_parameter_name,
p_value,
p_parameter_kind
);
end;
procedure override_input_parameters
(
p_audit_execution_id in number,
p_parameters varchar2,
p_parameter_kind in number
)
is
l_anchor_offset number := 1;
l_start_offset number := 1;
l_equals_offset number;
l_comma_offset number;
l_value_offset number;
l_esc_offset number;
l_esc_count number;
l_esc_char varchar2(4);
l_parameter_name varchar2(4000);
l_parameter_value varchar2(4000);
function strip_escape
(
p_escapedString varchar2
)
return varchar2
is
l_strippedString varchar2(4000);
l_a_char varchar2(4);
l_b_char varchar2(4);
l_strip_offset number := 1;
begin
loop
exit when p_escapedString is null or l_strip_offset > length(p_escapedString);
l_a_char := SUBSTR(p_escapedString, l_strip_offset, 1);
if l_strip_offset = length(p_escapedString)
then
l_strippedString := l_strippedString || l_a_char;
exit;
else
if l_a_char = '\'
then
l_b_char := SUBSTR(p_escapedString, l_strip_offset + 1, 1);
if l_b_char = '\' or l_b_char = ','
then
l_strippedString := l_strippedString || l_b_char;
l_strip_offset := l_strip_offset + 1;
end if;
else
l_strippedString := l_strippedString || l_a_char;
end if;
end if;
l_strip_offset := l_strip_offset + 1;
end loop;
return l_strippedString;
end;
begin
loop
l_equals_offset := INSTR(p_parameters, '=', l_start_offset);
exit when l_equals_offset = 0;
l_start_offset := l_equals_offset + 1;
loop
l_comma_offset := INSTR(p_parameters, ',', l_start_offset);
if l_comma_offset = 0
then
l_comma_offset := length(p_parameters) + 1;
exit;
else
l_esc_count := 0;
l_esc_offset := l_comma_offset - 1;
loop
l_esc_char := SUBSTR(p_parameters, l_esc_offset, 1);
exit when l_esc_char != '\';
l_esc_count := l_esc_count + 1;
l_esc_offset := l_esc_offset - 1;
end loop;
if MOD(l_esc_count, 2) != 0
then
l_start_offset := l_comma_offset + 1;
else
exit;
end if;
end if;
end loop;
l_parameter_name := LTRIM(RTRIM(SUBSTR(p_parameters, l_anchor_offset, l_equals_offset - l_anchor_offset)));
l_parameter_value := strip_escape(SUBSTR(p_parameters, l_equals_offset + 1, l_comma_offset - (l_equals_offset + 1)));
-- Override Input Parameter
override_input_parameter(p_audit_execution_id, l_parameter_name, l_parameter_value, p_parameter_kind);
exit when l_comma_offset >= length(p_parameters)-1;
l_start_offset := l_comma_offset + 1;
l_anchor_offset := l_start_offset;
end loop;
end;
procedure override_custom_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_CUSTOM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
procedure override_system_input_params
(
p_audit_execution_id in number,
p_parameters varchar2
)
is
l_parameter_kind number := wb_rt_api_exec.PARAMETER_KIND_SYSTEM;
begin
override_input_parameters(p_audit_execution_id, p_parameters, l_parameter_kind);
null;
end;
begin
execute immediate ('alter session set current_schema = ' || p_repos_owner) ;
--
-- Initialize Return Code
l_return := wb_rt_api_exec.RESULT_FAILURE;
--
-- Import Parameters
dbms_output.put_line('Stage 1: Decoding Parameters');
l_task_type_name := p_task_type ;
if UPPER(l_task_type_name) = 'PLSQL'
then
l_task_type := 'PLSQL';
elsif UPPER(l_task_type_name) = 'SQL_LOADER'
then
l_task_type := 'SQLLoader';
elsif UPPER(l_task_type_name) = 'PROCESS'
then
l_task_type := 'ProcessFlow';
else
l_task_type := l_task_type_name;
end if;
l_task_name := p_task_name ;
l_location_name := p_location_name ;
dbms_output.put_line('| location_name=' || l_location_name);
dbms_output.put_line('| task_type=' || l_task_type);
dbms_output.put_line('| task_name=' || l_task_name);
--
-- Decode Parameters
begin
--
-- Prepare Execution
--
dbms_output.put_line('Stage 2: Opening Task');
l_audit_execution_id := wb_rt_api_exec.open(l_task_type, l_task_name, l_location_name);
dbms_output.put_line('| l_audit_execution_id=' || to_char(l_audit_execution_id));
commit;
--
-- Override Parameters
--
dbms_output.put_line('Stage 3: Overriding Parameters');
override_system_input_params(l_audit_execution_id, p_system_params);
override_custom_input_params(l_audit_execution_id, p_custom_params);
--
-- Execute
--
dbms_output.put_line('Stage 4: Executing Task');
l_audit_result := wb_rt_api_exec.execute(l_audit_execution_id);
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_audit_result_disp := 'SUCCESS';
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_audit_result_disp := 'WARNING';
elsif l_audit_result = wb_rt_api_exec.RESULT_FAILURE
then
l_audit_result_disp := 'FAILURE';
else
l_audit_result_disp := 'UNKNOWN';
end if;
dbms_output.put_line('| l_audit_result=' || to_char(l_audit_result) || ' (' || l_audit_result_disp || ')');
-- Finish Execution
dbms_output.put_line('Stage 5: Closing Task');
wb_rt_api_exec.close(l_audit_execution_id);
commit;
dbms_output.put_line('Stage 6: Processing Result');
if l_oem_style
then
if l_audit_result = wb_rt_api_exec.RESULT_SUCCESS
then
l_return := 0;
elsif l_audit_result = wb_rt_api_exec.RESULT_WARNING
then
l_return := 0;
else
l_return := l_audit_result;
end if;
else
l_return := l_audit_result;
end if;
dbms_output.put_line('| exit=' || to_char(l_return));
exception
when no_data_found
then
raise_application_error(-20001, 'Task not found - Please check the Task Type, Name and Location are correct.');
end;
return l_return ;
end;
/
Then, grant EXECUTE on this function to the schema that will submit the job:
SQL> grant execute on run_owb_job to rt92;Grant succeeded.
Then, assuming your Runtime Repository schema is RTR92, your job you wish to run is a process flow and is in the WH_PROC_LOC location, the process flow is called LOAD_T_TIME and you want it to run on 25-APR-2005 at 17:20, run the following script
declare jobno number;
begin
dbms_job.submit(jobno, 'declare res number; begin res := rtr92.run_owb_job(''rtr92'',
''WH_PROC_LOC'',''PROCESS'',''LOAD_T_TIME''); end;', to_date('25-APR-2005 17:20','DD-MON-YYYY HH24:MI'));
commit;
end;
/
You can check what jobs are due to run by querying DBA_JOBS:
select * from all_jobs;
If you want the job to run on a regular basis, you can put an interval after the scheduled time to run, such as:
declare jobno number;
begin
dbms_job.submit(jobno, 'declare res number; begin res := rtr92.run_owb_job(''rtr92'',
''WH_PROC_LOC'',''PROCESS'',''LOAD_T_TIME''); end;', to_date('25-APR-2005 17:20','DD-MON-YYYY HH24:MI'),'SYSDATE +1');
commit;
end;
Note that the second parameter in the DBMS_JOB call is an anonymous PL/SQL block that calls the RUN_OWB_JOB function. The PL/SQL that is being executed is:
declare res number;
begin
res := rtr92.run_owb_job('rtr92','WH_PROC_LOC','PROCESS','LOAD_SALES');
end;
Also if you are using Oracle Database 10g you might want to replace DBMS_JOB with DBMS_SCHEDULER, which comes with a number of new features and is the "native" scheduling service in OWB "Paris".