Oracle Apex: Set Value Dynamic Action Examples

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

Usually, we set the value for an item or for multiple items, after a change or lost focus event on a particular item. For the below examples, I will use the Customer ID field and whenever the user changes the value of customer id field, it will set the value for customer first name and last name and other fields values.

Example-1: Set Value for Multiple Items Using SQL Statement Dynamic Action

In the below example, I will set the values for fields CUST_FIRST_NAME, CUST_LAST_NAME, on change event for the CUSTOMER_ID field.

To do this, I have created a dynamic action on CUSTOMER_ID field and have set the following settings:

Then for the Set Value dynamic action, I have set the following settings:

Set Type: SQL Statement
Items to Submit: P18_CUSTOMER_ID
Affected Elements > Selection Type: Item(s)
Item(s): P18_CUST_FIRST_NAME,P18_CUST_LAST_NAME
Fire on Initialization: No
SQL Statement: See below

select cust_first_name, cust_last_name from demo_customers
where customer_id = :p18_customer_id

Below is the screenshot of the above settings:

Now save the changes and run the page to test.

Example-2: Set Value using PL/SQL Function Body

Using the PL/SQL function body, you can return only one value, so you can set one field's value or set multiple fields value with the same returned value. Below are the screenshot and the PL/SQL code for this example:

declare
v_cust_first_name demo_customers.cust_first_name%type;

begin
   select cust_first_name into v_cust_first_name from demo_customers
where customer_id = :p18_customer_id;

    return v_cust_first_name;

exception
   when others then 
      return null;
end;

Example-3: Set Value Using JavaScript Expression

Same as above, you can specify a valid JavaScript expression and set the affected items to set the value for the items using the JavaScript expression. Below is an example to set the 10% commission on the total sales using JavaScript: