Dynamic Action Example to Set Value in Oracle Apex

In this tutorial, you will learn how to set value for an item using dynamic action in Oracle Apex.

You can set the value in Oracle Apex using the following set type methods:

Set Types for action Set Value

1. Static Assignment: Set a single static value.
2. JavaScript Expression: Set one or more values derived or calculated from JavaScript.
3. SQL Statement: Set one or more values, based on the result of a SQL query.
4. PL/SQL Expression: Set a single value, based on the result of a PL/SQL expression.
5. PL/SQL Function Body: Set a single value, based on the result of a PL/SQL function body.
6. Dialog Return Item: Set a single value, based on the returned item of a dialog. Note: This type only works if the dynamic action fires for the Dialog Closed event.

Oracle Apex Set Value Dynamic Action Examples

You can choose any event based on an item, button, etc. to set an item’s value. For example, you may require to set the value on the select list change event or the button click.

The following are the basic steps:

1. Create a dynamic action on an item, page load, or button click event, etc.
2. Set the action type as Set Value.
3. Define a Set Type.
4. Return the value according to the Set Type.
5. Specify items in Items to Submit if used in the Set Type method.
6. Specify the Affected Elements Selection type, usually Items.
7. Specify the Affected Items.

Below I am giving the most commonly used Set Type methods for the Set Value dynamic action.

Static Assignment Example

The following is an example of a static assignment type set value. On execution, it will set the P2_STATUS_FLAG value to Y.

Select an item, button, or select the event as per your need and create a dynamic action. Then set the following properties for it:

• Action: Set Value
• Set Type: Static Assignment
• Value: Y
• Affected Elements: Item(s)
• Item(s): P2_STATUS_FLAG

JavaScript Expression Example

The following is an example of a JavaScript expression type set value. It will copy the P2_EMPNO value to the P2_EMPNO2 item.

• Action: Set Value
• Set Type: JavaScript Expression
• JavaScript Expression: $v("P2_EMPNO")
• Affected Elements: Item(s)
• Item(s): P2_EMPNO2

SQL Statement Example

The following is an example of a SQL query type set value. The SQL query will get the Job on behalf of P2_EMPNO and will set the P2_JOB value.

• Action: Set Value
• Set Type: SQL Statement
• SQL Statement: select job from emp where empno = :p2_empno
• Items to Submit: P2_EMPNO
• Affected Elements: Item(s)
• Item(s): P2_JOB

You can notice above that we have specified the P2_EMPNO for the Items to Submit property because we used the P2_EMPNO in the SQL query. So whatever page items you use, specify it for the Items to Submit.

If multiple return values for multiple items. Check the below example:

• Action: Set Value
• Set Type: SQL Statement
• SQL Statement: select job, mgr from emp where empno = :p2_empno
• Items to Submit: P2_EMPNO
• Affected Elements: Item(s)
• Item(s): P2_JOB,P2_MGR

PL/SQL Expression Example

The following is an example of a PL/SQL expression type set value. It will set the date and time for the page item P2_CURRENT_DATE.

• Action: Set Value
• Set Type: PL/SQL Expression
• PL/SQL Expression: to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss')
• Affected Elements: Item(s)
• Item(s): P2_CURRENT_DATE

PL/SQL Function body Example

The following is an example of a PL/SQL function body type set value. The below PL/SQL function will return the mgr for the given employee number.

• Action: Set Value
• Set Type: PL/SQL Function body
• PL/SQL Function Body:

Declare
  n_mgr emp.mgr%type;
Begin
  Select mgr into n_mgr
    from emp where empno = :p2_empno;
   return n_mgr;
exception
   when others then 
    return null;
End;

• Items to Submit: P2_EMPNO
• Affected Elements: Item(s)
• Item(s): P2_MGR

I hope these examples will help you to create dynamic action to set the value for page items.