Oracle Apex: Set Page Item Value Using PL/SQL

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:

Oracle Apex page items.

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:

Oracle Apex set page item value using PL/SQL.

See also:

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

4 Comments

  1. 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:

      apex_util.set_session_state('YOUR_CREATED_BY_FIELD', sysdate);
      apex_util.set_session_state('YOUR_USERNAME_FIELD', :app_user);
      
    • 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;

  2. 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

Comments are closed.