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:
