Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 


Scheduling an OWB 9.0.4/9.2/10.1 Mapping or Process Flow Using DBMS_JOB
July 7, 2005
Mark Rittman

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


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational