PL/SQL API

Flow Instances are controlled in Flows for APEX through the PL/SQL API.

API Commands fall into the following categories:

• Instance operations, to create, start, stop, reset, terminate and delete an instance
• Step operations, to control forward progress through an Instance
• Instance Variable (Process Variable) operations, to set, get, and delete Instance state.

flow_api_pkg

flow_create Function Signature 1

This function creates a new process instance based on a diagram name and version (process specification)

If the version is not specified:

• first lookup is to use dgrm_status = ‘released’
• second lookup is to use dgrm_version = ‘0’ and dgrm_status = ‘draft’

If nothing is found based on above rules an exception will be raised. For accuracy, it’s recommended that you specify a version or use the form of flow_create specifying dgrm_id directly.

This function returns the process ID of the newly created process instance.

Syntax

flow_api_pkg.flow_create(
    pi_dgrm_name    in flow_diagrams.dgrm_name%type
  , pi_dgrm_version in flow_diagrams.dgrm_version%type default null
  , pi_prcs_name    in flow_processes.prcs_name%type
)
return flow_processes.prcs_id%type;

Parameters

Name	Description
pi_dgrm_name	Name of the model to instanciate
pi_dgrm_version	Version of the model to instanciate (optional)
pi_prcs_name	Name of the process instance to create

Example

This example will create a new process instance called “My Instance Name” based on the model “My Model” in version “0”.

declare
   l_prcs_id flow_processes.prcs_id%type;
begin
   l_prcs_id := flow_api_pkg.flow_create(
         pi_dgrm_name    => 'My Model'
      , pi_dgrm_version => '0'
      , pi_prcs_name    => 'My Instance Name'
   );
end;

flow_create Function Signature 2

This function creates a new process instance based on a diagram ID (process specification) and returns the process ID of the newly created process instance.

Syntax

flow_api_pkg.flow_create(
    pi_dgrm_id   in flow_diagrams.dgrm_id%type
  , pi_prcs_name in flow_processes.prcs_name%type
)
return flow_processes.prcs_id%type;

Parameters

Name	Description
pi_dgrm_id	ID of the model to instanciate
pi_prcs_name	Name of the process instance to create

Example

This example will create a new process instance called “My Instance Name” based on the model ID 1.

declare
   l_prcs_id flow_processes.prcs_id%type;
begin
   l_prcs_id := flow_api_pkg.flow_create(
         pi_dgrm_id    => 1
      , pi_prcs_name    => 'My Instance Name'
   );
end;

flow_create Procedure Signature 1

This procedure creates a new process instance based on a diagram name and version (process specification)

If the version is not specified:

• first lookup is to use dgrm_status = ‘released’
• second lookup is to use dgrm_version = ‘0’ and dgrm_status = ‘draft’

If nothing is found based on above rules an exception will be raised. For accuracy, it’s recommended that you specify a version or use the form of flow_create specifying dgrm_id directly.

Syntax

flow_api_pkg.flow_create(
    pi_dgrm_name    in flow_diagrams.dgrm_name%type
  , pi_dgrm_version in flow_diagrams.dgrm_version%type default null
  , pi_prcs_name    in flow_processes.prcs_name%type
)
return flow_processes.prcs_id%type;

Parameters

Name	Description
pi_dgrm_name	Name of the model to instanciate
pi_dgrm_version	Version of the model to instanciate (optional)
pi_prcs_name	Name of the process instance to create

Example

This example will create a new process instance called “My Instance Name” based on the model “My Model” in version “0”.

begin
   flow_api_pkg.flow_create(
         pi_dgrm_name    => 'My Model'
      , pi_dgrm_version => '0'
      , pi_prcs_name    => 'My Instance Name'
   );
end;

flow_create Procedure Signature 2

This procedure creates a new process instance based on a diagram ID (process specification).

Syntax

flow_api_pkg.flow_create(
    pi_dgrm_id   in flow_diagrams.dgrm_id%type
  , pi_prcs_name in flow_processes.prcs_name%type
);

Parameters

Name	Description
pi_dgrm_id	ID of the model to instanciate
pi_prcs_name	Name of the process instance to create

Example

This example will create a new process instance called “My Instance Name” based on the model ID 1.

begin
   flow_api_pkg.flow_create(
         pi_dgrm_id    => 1
      , pi_prcs_name    => 'My Instance Name'
   );
end;

flow_start Procedure

This procedure is use to start a process that was previously created by flow_create. It will will find the none Start event in the Flow diagram, and step the instance forward from the Start event.

Syntax

flow_api_pkg.flow_start(
    p_process_id in flow_processes.prcs_id%type
);

Parameters

Name	Description
p_process_id	Process ID to start

Example

This example will create a new process instance called “My Instance Name” based on the model “My Model” in version “0” and start it.

declare
   l_prcs_id flow_processes.prcs_id%type;
begin
   l_prcs_id := flow_api_pkg.flow_create(
        pi_dgrm_name    => 'My Model'
      , pi_dgrm_version => '0'
      , pi_prcs_name    => 'My Instance Name'
   );

   flow_api_pkg.flow_start(
      p_process_id => l_prcs_id
   );
end;

flow_terminate Procedure

This procedure is used, typically by an administrator, to permanently stop processing of the Instance (which will be lefty in a status of terminated).

Syntax

flow_api_pkg.flow_terminate(
     p_process_id  in flow_processes.prcs_id%type
   , p_comment     in flow_instance_event_log.lgpr_comment%type default null
);

Parameters

Name	Description
p_process_id	Process ID to terminate
p_comment	Optional comment to be added to the instance event logs

Example

This example will terminate the process instance that have the ID 1.

begin
   flow_api_pkg.flow_terminate(
        p_process_id    => 1
      , p_comment => 'Process Instance Terminated using the PL/SQL API'
   );
end;

flow_reset Procedure

This procedure is used, typically during model development and testing only, to reset an existing Instance to the state it would be at after it was created, but not yet started. All Process Variables, except built-ins, are deleted. This is not meant for use in Production Systems.

Syntax

flow_api_pkg.flow_reset(
     p_process_id  in flow_processes.prcs_id%type
   , p_comment     in flow_instance_event_log.lgpr_comment%type default null
);

Parameters

Name	Description
p_process_id	Process ID to reset
p_comment	Optional comment to be added to the instance event logs

Example

This example will reset the process instance that have the ID 1.

begin
   flow_api_pkg.flow_reset(
        p_process_id    => 1
      , p_comment => 'Reset Process Instance using the PL/SQL API'
   );
end;

flow_delete Procedure

This procedure is used to remove an Instance from the system. All instances, subflows, and variables are deleted from the operational system. If event logging was enabled while the process was running, records in the event logs are maintained for auditting purposes.

Syntax

flow_api_pkg.flow_delete(
     p_process_id  in flow_processes.prcs_id%type
   , p_comment     in flow_instance_event_log.lgpr_comment%type default null
);

Parameters

Name	Description
p_process_id	Process ID to delete
p_comment	Optional comment to be added to the instance event logs

Example

This example will delete the process instance that have the ID 1.

begin
   flow_api_pkg.flow_reset(
        p_process_id => 1
      , p_comment    => 'Delete Process Instance using the PL/SQL API'
   );
end;

flow_start_step Procedure

This procedure is an optional command that can be used in applications to signal that a user is about to start working on a task. This is only used to differentiate ‘waiting’ time from ‘processing’ time in process management and statistics. A ‘good-practice’ app would issue this call when work is about to start on a task.

Syntax

flow_api_pkg.flow_start_step(
     p_process_id  in flow_processes.prcs_id%type
   , p_subflow_id  in flow_subflows.sbfl_id%type default null
   , p_step_key    in flow_subflows.sbfl_step_key%type default null
);

Parameters

Name	Description
p_process_id	Process ID
p_subflow_id	Subflow ID
p_step_key	Step Key

Example

This example will use this procedure to indicate that the work is started for the task that have the subflow ID 3 in the process ID 1.

begin
   flow_api_pkg.flow_start_step(
        p_process_id => 1
      , p_subflow_id => 3 
      , p_step_key   => 'NqOiEHUbAF'
   );
end;

flow_reserve_step Procedure

This procedure is used to signal to other users that a user is going to handle this task (see the documentation on reservations).

Syntax

flow_api_pkg.flow_reserve_step(
     p_process_id  in flow_processes.prcs_id%type
   , p_subflow_id  in flow_subflows.sbfl_id%type default null
   , p_step_key    in flow_subflows.sbfl_step_key%type default null
   , p_reservation in flow_subflows.sbfl_reservation%type
);

Parameters

Name	Description
p_process_id	Process ID
p_subflow_id	Subflow ID
p_step_key	Step Key
p_reservation	Value of the reservation, typically the user name

Example

This example will reserve the step in proces ID 1 and subflow ID 3 for the current user.

begin
   flow_api_pkg.flow_reserve_step(
        p_process_id  => 1
      , p_subflow_id  => 3 
      , p_step_key    => 'NqOiEHUbAF'
      , p_reservation => :APP_USER
   );
end;

flow_release_step Procedure

This procedure is used to remove a reservation (see the documentation on reservations).

Syntax

flow_api_pkg.flow_release_step(
     p_process_id  in flow_processes.prcs_id%type
   , p_subflow_id  in flow_subflows.sbfl_id%type default null
   , p_step_key    in flow_subflows.sbfl_step_key%type default null
);

Parameters

Name	Description
p_process_id	Process ID
p_subflow_id	Subflow ID
p_step_key	Step Key

Example

This example will release the reservation for a step in proces ID 1 and subflow ID 3 for the current user.

begin
   flow_api_pkg.flow_release_step(
        p_process_id => 1
      , p_subflow_id => 3 
      , p_step_key   => 'NqOiEHUbAF'
   );
end;

flow_complete_step Procedure

This procedure is used to tell the flow engine that the current step is complete, and to move the instance forward to the next step.

Syntax

flow_api_pkg.flow_complete_step(
     p_process_id  in flow_processes.prcs_id%type
   , p_subflow_id  in flow_subflows.sbfl_id%type default null
   , p_step_key    in flow_subflows.sbfl_step_key%type default null
);

Parameters

Name	Description
p_process_id	Process ID
p_subflow_id	Subflow ID
p_step_key	Step Key

Example

This example will complete a step in proces ID 1 and subflow ID 3 for the current user.

begin
   flow_api_pkg.flow_complete_step(
        p_process_id  => 1
      , p_subflow_id  => 3 
      , p_step_key    => 'NqOiEHUbAF'
   );
end;

flow_restart_step Procedure

This procedure is designed to be called by an administrator to restart, for example, a scriptTask or serviceTask that has failed due to an error. The intended usage is that the adminstrator can fix the script or edit the process data that caused the task to fail, and then restart the task using this call.
A comment can optionally be provided, which will be added to the task event log entry. It should only be used on a subflow having a status of ‘error’

Syntax

flow_api_pkg.flow_restart_step(
     p_process_id in flow_processes.prcs_id%type
   , p_subflow_id in flow_subflows.sbfl_id%type default null
   , p_step_key   in flow_subflows.sbfl_step_key%type default null
   , p_comment    in flow_instance_event_log.lgpr_comment%type default null
);

Parameters

Name	Description
p_process_id	Process ID
p_subflow_id	Subflow ID
p_step_key	Step Key
p_comment	Optional comment to be added to the event logs

Example

This example will restart a step in proces ID 1 and subflow ID 3.

begin
   flow_api_pkg.flow_restart_step(
        p_process_id  => 1
      , p_subflow_id  => 3
      , p_step_key    => 'NqOiEHUbAF' 
   );
end;

flow_reschedule_timer Procedure

flow_reschedule_timer can be called to change the next scheduled time of a timer on a running timer. It can change the currently scheduled time to a future time, or can instruct the timer to fire immediately. If the Timer event is a single event timer (timer types Date or Duration), it will change the time at which the event is scheduled to occur to the new time. If the timer is a Cycle Timer, this changes the time that the next firoing is scheduled to occur. If later cycles exist, the following cycle will be scheduled at the new firing time + the repeat interval. To fire the timer immediately, set p_is_immediate = true. To change the firing time to another future time, supply a new timestamp with time zone contaiing the new time. A comment can be provided, which is passed to the log file for explanation of any rescheduling.

Syntax

flow_api_pkg.flow_reschedule_timer(
    p_process_id    in flow_processes.prcs_id%type
  , p_subflow_id    in flow_subflows.sbfl_id%type
  , p_step_key      in flow_subflows.sbfl_step_key%type default null
  , p_is_immediate  in boolean default false
  , p_new_timestamp in flow_timers.timr_start_on%type default null
  , p_comment       in flow_instance_event_log.lgpr_comment%type default null
);

Parameters

Name	Description
p_process_id	Process ID
p_subflow_id	Subflow ID
p_step_key	Step Key
p_is_immediate	Causes the step to occur immediately
p_new_timestamp	Causes the step to be rescheduled at the supplied timestamp
p_comment	Optional comment to be added to the event logs

Example

This example causes a waiting step in proces ID 1 and subflow ID 3 to occur immediately.

begin
   flow_api_pkg.flow_reschedule_timer(
        p_process_id   => 1
      , p_subflow_id   => 3 
      , p_step_key     => 'NqOiEHUbAF'
      , p_is_immediate => true
   );
end;

This example causes a waiting step in proces ID 1 and subflow ID 3 to be rescheduled for a future time (+2 days).

begin
   flow_api_pkg.flow_reschedule_timer(
        p_process_id    => 1
      , p_subflow_id    => 3 
      , p_step_key      => 'NqOiEHUbAF'
      , p_new_timestamp => systimestamp + 2
   );
end;

get_current_usertask_url Function

This function returns the URL for the current task on the specified subflow.

Syntax

flow_api_pkg.get_current_usertask_url(
    p_process_id in flow_processes.prcs_id%type
  , p_subflow_id in flow_subflows.sbfl_id%type
  , p_step_key   in flow_subflows.sbfl_step_key%type default null
)
return varchar2;

Parameters

Name	Description
p_process_id	Process ID
p_subflow_id	Subflow ID
p_step_key	Step Key

Example

This example show how to get the current task url for process ID 1 and subflow ID 3

declare
   l_url varchar2(4000);
begin
   l_url := flow_api_pkg.get_current_usertask_url(
        p_process_id => 1
      , p_subflow_id => 3
      , p_step_key   => 'NqOiEHUbAF'
   );
end;

message Function

This function returns a Flows for APEX error message with p0…p9 substitutions in p_lang.

Syntax

function message( 
    p_message_key     in varchar2 
  , p_lang            in varchar2 default 'en'
  , p0                in varchar2 default null
  , p1                in varchar2 default null
  , p2                in varchar2 default null
  , p3                in varchar2 default null
  , p4                in varchar2 default null
  , p5                in varchar2 default null
  , p6                in varchar2 default null
  , p7                in varchar2 default null
  , p8                in varchar2 default null
  , p9                in varchar2 default null
  ) return varchar2;

Parameters

Name	Description
p_message_key	Key of the message that shoudl exists in the flow_messages table
p_lang	Lang code to retrieve the message, default is “en”
p0 through p9	Value for substitution: %0 in the message will be replaced by p0, %1 by p1, etc.

Example

This example show how to get the “gateway-no-route” message in french

declare
   l_message varchar2(4000);
begin
   l_message := flow_api_pkg.message(
        p_message_key => 'gateway-no-route'
      , p_lang        => 'fr'
      , p0            => 'Variable Name'
   );
end;

flow_process_vars

get_var_vc2 Function

This function is used to get the value of a VARCHAR2 process variable.

Syntax

flow_process_vars.get_var_vc2(
    pi_prcs_id           in flow_processes.prcs_id%type
  , pi_var_name          in flow_process_variables.prov_var_name%type
  , pi_exception_on_null in boolean default false
) return flow_process_variables.prov_var_vc2%type;

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_exception_on_null	Indicates if an exception is raised if the variable doesn’t exists (default false)

Example

This example will get the value of the process variable “MY_VAR”.

declare
   l_value flow_process_variables.prov_var_vc2%type;
begin
   l_value := flow_process_vars.get_var_vc2(
                   pi_prcs_id   => 1
                 , pi_var_name  => 'MY_VAR'
              );
end;

get_var_num Function

This function is used to get the value of a NUMBER process variable.

Syntax

flow_process_vars.get_var_num(
    pi_prcs_id           in flow_processes.prcs_id%type
  , pi_var_name          in flow_process_variables.prov_var_name%type
  , pi_exception_on_null in boolean default false
) return flow_process_variables.prov_var_num%type;

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_exception_on_null	Indicates if an exception is raised if the variable doesn’t exists (default false)

Example

This example will get the value of the process variable “MY_VAR”.

declare
   l_value flow_process_variables.prov_var_num%type;
begin
   l_value := flow_process_vars.get_var_num(
                   pi_prcs_id   => 1
                 , pi_var_name  => 'MY_VAR'
              );
end;

get_var_date Function

This function is used to get the value of a DATE process variable.

Syntax

flow_process_vars.get_var_date(
    pi_prcs_id           in flow_processes.prcs_id%type
  , pi_var_name          in flow_process_variables.prov_var_name%type
  , pi_exception_on_null in boolean default false
) return flow_process_variables.prov_var_date%type;

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_exception_on_null	Indicates if an exception is raised if the variable doesn’t exists (default false)

Example

This example will get the value of the process variable “MY_VAR”.

declare
   l_value flow_process_variables.prov_var_date%type;
begin
   l_value := flow_process_vars.get_var_date(
                   pi_prcs_id   => 1
                 , pi_var_name  => 'MY_VAR'
              );
end;

get_var_clob Function

This function is used to get the value of a CLOB process variable.

Syntax

flow_process_vars.get_var_clob(
    pi_prcs_id           in flow_processes.prcs_id%type
  , pi_var_name          in flow_process_variables.prov_var_name%type
  , pi_exception_on_null in boolean default false
) return flow_process_variables.prov_var_clob%type;

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_exception_on_null	Indicates if an exception is raised if the variable doesn’t exists (default false)

Example

This example will get the value of the process variable “MY_VAR”.

declare
   l_value flow_process_variables.prov_var_clob%type;
begin
   l_value := flow_process_vars.get_var_clob(
                   pi_prcs_id   => 1
                 , pi_var_name  => 'MY_VAR'
              );
end;

get_business_ref Function

This function is used to get the value of the built-in BUSINESS_REF process variable.

Syntax

flow_process_vars.get_business_ref(
    pi_prcs_id           in flow_processes.prcs_id%type
) return flow_process_variables.prov_var_vc2%type;

Parameters

Name	Description
pi_prcs_id	Process ID

Example

This example will get the value of the process variable “BUSINESS_REFERENCE” in process instance ID 1.

declare
   l_business_ref flow_process_variables.prov_var_vc2%type;
begin
   l_business_ref := flow_process_vars.get_business_ref( pi_prcs_id   => 1 );
end;

set_business_ref Procedure

This function is used to set the value of the built-in BUSINESS_REF process variable.

Syntax

flow_process_vars.set_business_ref(
     pi_prcs_id   in flow_processes.prcs_id%type
   , pi_vc2_value in flow_process_variables.prov_var_vc2%type
);

Parameters

Name	Description
pi_prcs_id	Process ID
pi_vc2_value	Value of the BUSINESS_REF to set

Example

This example will set the value of the process variable “BUSINESS_REFERENCE” in process instance ID 1.

begin
   flow_process_vars.set_business_ref( 
        pi_prcs_id   => 1 
      , pi_vc2_value => 'NEW_VALUE'
   );
end;

get_var_type Function

This function is used to get the type of the given process variable.

Syntax

flow_process_vars.get_var_type(
     pi_prcs_id           in flow_processes.prcs_id%type
   , pi_var_name          in flow_process_variables.prov_var_name%type
   , pi_exception_on_null in boolean default false
) return flow_process_variables.prov_var_type%type;

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_exception_on_null	Indicates if an exception is raised if the variable doesn’t exists (default false)

Example

This example will get the type of the process variable “MY_VARIABLE” in process instance ID 1.

declare
   l_prov_var_type flow_process_variables.prov_var_type%type;
begin
   l_prov_var_type := flow_process_vars.get_var_type( 
        pi_prcs_id   => 1 
      , pi_var_name => 'MY_VARIABLE'
   );
end;

set_var Procedure Signature 1

This procedure is used to set a VARCHAR2 value of a process variable.

Syntax

flow_process_vars.set_var(
    pi_prcs_id   in flow_processes.prcs_id%type
  , pi_var_name  in flow_process_variables.prov_var_name%type
  , pi_vc2_value in flow_process_variables.prov_var_vc2%type
);

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_vc2_value	Value of the process variable (VARCHAR2)

Example

This example will set the value of the process variable “MY_VAR” to “MY_VAR_VALUE” in the process instance ID 1.

begin
   flow_process_vars.set_var(
        pi_prcs_id   => 1
      , pi_var_name  => 'MY_VAR'
      , pi_vc2_value => 'MY_VAR_VALUE'
   );
end;

set_var Procedure Signature 2

This procedure is used to set a NUMBER value of a process variable.

Syntax

flow_process_vars.set_var(
    pi_prcs_id   in flow_processes.prcs_id%type
  , pi_var_name  in flow_process_variables.prov_var_name%type
  , pi_num_value in flow_process_variables.prov_var_num%type
);

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_num_value	Value of the process variable (NUMBER)

Example

This example will set the value of the process variable “MY_VAR” to 1234 in the process instance ID 1.

begin
   flow_process_vars.set_var(
        pi_prcs_id   => 1
      , pi_var_name  => 'MY_VAR'
      , pi_num_value => 1234
   );
end;

set_var Procedure Signature 3

This procedure is used to set a DATE value of a process variable.

Syntax

flow_process_vars.set_var(
    pi_prcs_id   in flow_processes.prcs_id%type
  , pi_var_name  in flow_process_variables.prov_var_name%type
  , pi_date_value in flow_process_variables.prov_var_date%type
);

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_date_value	Value of the process variable (DATE)

Example

This example will set the value of the process variable “MY_VAR” to sysdate in the process instance ID 1.

begin
   flow_process_vars.set_var(
        pi_prcs_id   => 1
      , pi_var_name  => 'MY_VAR'
      , pi_num_value => sysdate
   );
end;

set_var Procedure Signature 4

This procedure is used to set a CLOB value of a process variable.

Syntax

flow_process_vars.set_var(
    pi_prcs_id   in flow_processes.prcs_id%type
  , pi_var_name  in flow_process_variables.prov_var_name%type
  , pi_clob_value in flow_process_variables.prov_var_clob%type
);

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable
pi_clob_value	Value of the process variable (CLOB)

Example

This example will set the value of the process variable “MY_VAR” to “This is a long text.” in the process instance ID 1.

begin
   flow_process_vars.set_var(
        pi_prcs_id   => 1
      , pi_var_name  => 'MY_VAR'
      , pi_num_value => to_clob('This is a long text')
   );
end;

delete_var

This procedure is used to delete a process variable

Syntax

flow_process_vars.delete_var(
    pi_prcs_id           in flow_processes.prcs_id%type
  , pi_var_name          in flow_process_variables.prov_var_name%type
);

Parameters

Name	Description
pi_prcs_id	Process ID
pi_var_name	Name of the process variable

Example

This example will delete the process variable “MY_VAR” in process instance ID 1.

begin
   flow_process_vars.delete_var(
        pi_prcs_id   => 1
      , pi_var_name  => 'MY_VAR'
   );
end;