/* 

Script should work with OWB 9.0.4.10 or higher.

 USAGE

 p_repos_owner   := e.g. MY_RUNTIME    - Name of the Runtime Repository Owner

 p_location_name := e.g. MY_WAREHOUSE  - Physical Name of the Location to which this task was deployed
                                         (i.e. a DB Location or a Process Location or the Platform Schema)
                                         Note: Always use "PlaformSchema" for SQL_LOADER types.

 p_task_type     := PLSQL              - OWB PL/SQL Mapping
                 |  SQL_LOADER         - OWB SQL*Loader Mapping
                 |  PROCESS            - OWB ProcessFlow

 p_task_name     := e.g. MY_MAPPING    - Physical Name of the Deployed Object

 p_system_params := { , | (name = value [, name = value]...)}
                    e.g. ","
                    or   MY_PARAM=1,YOUR_PARAM=true

 p_custom_params := { , | (name = value [, name = value]...)}
                    e.g. ","
                    or   MY_PARAM=1,YOUR_PARAM=true

 RETURNS

   l_resut = 1 if task reports SUCCESS, 2 if WARNING, 3 if ERROR

How to use install and use procedure:

   - Logon to runtime repository owner and run this script. This creates a procedure run_owb_code
   - grant execute to run_owb_code to <runtime access user>
   - connect as runtime access user and run something along the lines of:
declare

  l_result number ;
  l_audit_id number ;

begin

  <runtime repository owner>.run_owb_code( l_result
                                         , l_audit_id
                                         , '<runtime repository owner>'
                                         , '<target location>'
                                         , '<type: PLSQL, PROCESS or SQL_LOADER>'
                                         , '<mapping or process name>'
                                         , '<system parameters>'
                                         , '<custom parameters>'
                                         ) ;

end ;

Because of the defaults you can leave any input parameters into
run_owb_code empty, such as p_system_parameters and
p_custom_parameters. For an implementation, consider changing the
defaults (such as p_repos_owner) to make live easier. Also, consider
taking out the dbms_output.put_line messages in a production situation.

To run this script, a user has to have been granted roles WB_R_<runtime
repos owner> and WB_U_<runtime repos owner>. The runtime access user
will have these roles granted.

*/


create or replace procedure run_owb_code
              ( p_result out number
              , p_audit_id out number
              , 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
              )
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;

  p_result := l_return ;
  p_audit_id := l_audit_execution_id ;

end;
/