Home » OrclApex » Oracle Apex: Set Page Item Value Using PL/SQL

Oracle Apex: Set Page Item Value Using PL/SQL

By

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: