This Oracle Apex tutorial shows you, how to set page item value using PL/SQL. Here I am giving two examples of two types of Dynamic Actions in Oracle Apex. One type of dynamic action is "Set Value
" using PL/SQL code and another type of dynamic action is "Execute PL/SQL Code
".
Before starting the examples, first, understand the page items used in both examples. I have a page 12, with a region named "Employees Info" with two Page Items (1) P12_EMPNO
(2) P12_EMPLOYEE_NAME
. Also, I created two buttons to execute both types of dynamic actions I mentioned above. Below is the screenshot of the same:
Below is the table which I am using in this example:
CREATE TABLE employees ( employee_id NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL , CONSTRAINT emp_email_uk UNIQUE (email) , phone_number VARCHAR2(20) , hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL , job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL , salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0) , commission_pct NUMBER(2,2) , manager_id NUMBER(6) , CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees , department_id NUMBER(4) , CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments ) /
You can create the above table in your schema to test. Also, insert some data as follows:
INSERT INTO employees VALUES ( 100 , 'Steven' , 'King' , 'SKING' , '515.123.4567' , TO_DATE('17-06-2003', 'dd-MM-yyyy') , 'AD_PRES' , 24000 , NULL , NULL , 90 ); INSERT INTO employees VALUES ( 101 , 'Neena' , 'Kochhar' , 'NKOCHHAR' , '515.123.4568' , TO_DATE('21-09-2005', 'dd-MM-yyyy') , 'AD_VP' , 17000 , NULL , 100 , 90 ); INSERT INTO employees VALUES ( 102 , 'Lex' , 'De Haan' , 'LDEHAAN' , '515.123.4569' , TO_DATE('13-01-2001', 'dd-MM-yyyy') , 'AD_VP' , 17000 , NULL , 100 , 90 ); Commit;
1. Set Page Item Value using SET VALUE (PL/SQL) Dynamic Action in Oracle Apex
Do the right-click on the first button and select Create Dynamic Action. Set the following properties:
- Action: Set Value
- Set Type: PL/SQL Function Body
- Items to Submit:
P12_EMPNO
- Affected Elements > Selection Type: Item(s)
- Affected Elements > Item(s):
P12_EMPLOYEE_NAME
Then add the following code in PL/SQL Function Body:
declare v_empname varchar2(200); begin select first_name ||' '|| last_name into v_empname from employees where employee_id = :P12_EMPNO; return v_empname; exception when others then return null; end;
The above PL/SQL code will get the employee's first name and last name against the employee number being entered in the Page Item P12_EMPNO
. It will return the employee name and the effected Page Item is P12_EMPLOYEE_NAME
.
2. Oracle Apex Dynamic Action Execute PL/SQL Code to Set Page Item Value
Now do the right-click on the second button and select Create Dynamic Action. Set the following properties:
- Action: Execute PL/SQL Code
- Items to Submit:
P12_EMPNO
- Items to Return:
P12_EMPLOYEE_NAME
- PL/SQL Code: (Add the following code into it)
declare v_empname employees.first_name%type; begin select first_name into v_empname from employees where employee_id = :P12_EMPNO; apex_util.set_session_state('P12_EMPLOYEE_NAME', v_empname); exception when others then apex_util.set_session_state('P12_EMPLOYEE_NAME', null); end;
The above PL/SQL code is doing the same thing but the difference is, instead of returning the employee name, it is setting the Page Item value through the procedure APEX_UTIL.SET_SESSION_STATE
.
You can also use the procedure APEX_UTIL.SET_SESSION_STATE
in a process in Oracle Apex to set a page item value on page submit.
Now save the changes and run the page. Enter the employee's id and click any of the buttons and you will get the name as shown in the below image:
How can I implement oracle form (not database level) PRE-INSERT and PRE-UPDATE trigger in Oracle APEX.
My requirement is to store the user name , date in column like CREATED_BY/MODIFIED_BY and CREATED_ON/MODIFIED_ON of the table
To do this, click on the process tab and create a process before any other processes.
Then add the following two lines of PL/SQL code:
Thanks .
It works perfectly. To avoid overwrite the "Created by" and "Created on" during edit I have added a IF condition .
IF :P5_UOM_CODE IS NULL THEN
apex_util.set_session_state('P5_CREATED_BY', :app_user);
apex_util.set_session_state('P5_CREATED_ON', TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'));
ELSE
apex_util.set_session_state('P5_MODIFIED_BY', :app_user);
apex_util.set_session_state('P5_MODIFIED_ON', TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'));
END IF;
Hi..
How can I set a value of an item ,this item value consists of a concatenation of the years of two dates,for example:
plan start date : 1/04/2021
plan end date : 31/03/2022
so the value of plan id would be '2021-2022'
I did the same as you did in your first example of 'set value' in this tutorial, but nothing was generated!! the value was blank!!
Thanks