Rittman Mead - Delivered Intelligence

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”.

Posted in Oracle Warehouse Builder | 7 Comments »