mardi 7 août 2012

How to Execute Process Flow from SQL*Plus

1. Introduction :
Process flow allows us to execute mappings, tranformations ...
Using Sql*Plus, you can execute the process flows, and you can find belw the scripts to use for 9i, 10g and 11g.

2. Scripts
 2.1 9i
 First create the Run_Owb_Job function as :


CREATE OR REPLACE FUNCTION RUN_OWB_OWNER_9204."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 ');
    dbms_output.put_line('Stage 2:' || l_task_type);
    dbms_output.put_line('Stage 2:' || l_task_name);
    dbms_output.put_line('Stage 2:' || l_location_name);
    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;
/


Second, create the FUN_LAUNCH_OWB function to run with sqlplus :


CREATE OR REPLACE FUNCTION "FUN_LAUNCH_OWB" RETURN NUMBER AS
res number;
BEGIN
res := run_owb_job('RUN_OWB_OWNER_9204','MAIN_DEPLOY','PROCESS','WF_MAIN');
  RETURN 0;
END FUN_LAUNCH_OWB;
/
2.2 10g

First create the Run_Owb_Job function as :


CREATE OR REPLACE function REP_OWB_OWNER.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 ');
    dbms_output.put_line('Stage 2:' || l_task_type);
    dbms_output.put_line('Stage 2:' || l_task_name);
    dbms_output.put_line('Stage 2:' || l_location_name);
    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;
/



second create the FUN_LAUNCH_OWB function to run with sqlplus :
CREATE OR REPLACE FUNCTION REP_OWB_OWNER."FUN_LAUNCH_OWB" RETURN NUMBER AS
res number;
BEGIN
res := run_owb_job('REP_OWB_OWNER','OWF_LOCATION','PROCESS','FI_MAIN');
  RETURN 0;
END FUN_LAUNCH_OWB;
/
2.3 11g
 create the FUN_LAUNCH_OWB function to run with sqlplus :

CREATE OR REPLACE FUNCTION OWBSYS."FUN_LAUNCH_OWB" RETURN
    NUMBER AS
res number;
BEGIN

  OWBSYS.wb_rt_script_util.set_workspace('REP_OWB_OWNER.WORKSPACE_OWNER');
  res := OWBSYS.wb_rt_script_util.run_task( 
                                            'LOCATION_WORKFLOW_11GR2',
                                                         'PROCESSFLOW',
                                                         'WF_MAIN',
                                                         NULL,
                                                         NULL,
                                                         0,
                                                         0);
  RETURN 0;
  
END FUN_LAUNCH_OWB;
/




Publié par à
Libellés :

1 commentaire:

  1. caciexue4 mars 2022 à 09:53

    MEGA Millions Casino Promo Code for November 2021
    The MEGA Millions code and promo code are available now for all US players. 울산광역 출장안마 Use the promo 김천 출장마사지 code MEGA1000 이천 출장안마 for $5 천안 출장마사지 free + a $1000 deposit 안양 출장마사지 bonus.

    RépondreSupprimer

Inscription à : Publier les commentaires (Atom)