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;
$v("P18_TOTAL_SALES") * 0.10;
In this case, for example, I will set the affected item as P18_COMMISSON.