🔄 How to Declare and Use a PL/SQL Procedure in Oracle APEX

Working with stored procedures in Oracle APEX enables powerful database-driven actions without complex front-end logic. In this tutorial, you’ll learn how to:

• Create two tables (depart and employ)
• Write a PL/SQL procedure to transfer employees
• Trigger that procedure using a button in Oracle APEX

🧱 Step 1: Create Tables in Oracle APEX SQL Workshop

Navigate to SQL Workshop > SQL Commands in Oracle APEX and run the following script to create and populate your tables:

-- Create department table
CREATE TABLE depart (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

-- Create employee table
CREATE TABLE employ (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    department_id NUMBER,
    FOREIGN KEY (department_id) REFERENCES depart(department_id)
);

-- Insert department data
INSERT INTO depart (department_id, department_name) VALUES (1, 'Sales');
INSERT INTO depart (department_id, department_name) VALUES (2, 'Engineering');
INSERT INTO depart (department_id, department_name) VALUES (3, 'HR');

-- Insert employee data
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (101, 'Alice', 1);
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (102, 'Bob', 2);
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (103, 'Charlie', 1);

COMMIT;

🔧 Step 2: Create the Stored Procedure

Still in SQL Workshop > SQL Commands, run this PL/SQL procedure:

CREATE OR REPLACE PROCEDURE transfer_employee (
    p_employee_id IN NUMBER,
    p_new_department_id IN NUMBER
) IS
    v_old_department_id NUMBER;
BEGIN
    SELECT department_id INTO v_old_department_id
    FROM employ
    WHERE employee_id = p_employee_id;

    UPDATE employ
    SET department_id = p_new_department_id
    WHERE employee_id = p_employee_id;

    DBMS_OUTPUT.PUT_LINE('Employee ' || p_employee_id || ' moved from department ' || v_old_department_id || ' to department ' || p_new_department_id);

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee with ID ' || p_employee_id || ' does not exist.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END transfer_employee;
/

🖱️ Step 3: Create a Page with a Button in Oracle APEX

Go to App Builder > Open your app.

Click Create Page > Choose Form > Select Form on Table with Report.

Use employ as the table.

Once the page is created, add two Page Items:

• PXX_EMPLOYEE_ID (Text Field)
• PXX_NEW_DEPARTMENT_ID (Text Field)

Replace PXX with your page number.

🛠️ Step 4: Create a Button and Process to Call Procedure

Create the Button

1. In Page Designer, select Buttons.
2. Click + to add a button, name it Transfer.
3. Set Action to “Submit Page”.

Create the Process

1. Under Processing, click + > Select Process > Name it Transfer Employee.
2. Type: PL/SQL Code
3. Location: After Submit
4. Code:

BEGIN
  transfer_employee(:PXX_EMPLOYEE_ID, :PXX_NEW_DEPARTMENT_ID);
END;

Replace PXX with your page number.

Server-Side Condition: When Button Pressed = Transfer

✅ Step 5: Run and Test the App

1. Run the app.
2. Enter an employee ID (e.g., 101) and new department ID (e.g., 3).
3. Click Transfer.

Check the database to confirm the department change in the employ table.

🏷 Suggested SEO Tags

• Oracle APEX PL/SQL Procedure
• Oracle APEX Button to Call Procedure
• Oracle APEX Form Trigger Procedure
• How to Declare Procedure in Oracle APEX
• Transfer Employee Procedure in Oracle
• Oracle APEX Tutorial for Beginners
• Oracle Stored Procedure UI Trigger

How to declare Procedure in Oracle Apex

-- Create the depart table
CREATE TABLE depart (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

-- Create the employ table
CREATE TABLE employ (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(50),
    department_id NUMBER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert into departments
INSERT INTO depart (department_id, department_name) VALUES (1, 'Sales');
INSERT INTO depart (department_id, department_name) VALUES (2, 'Engineering');
INSERT INTO depart (department_id, department_name) VALUES (3, 'HR');

-- Insert into employees
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (101, 'Alice', 1);
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (102, 'Bob', 2);
INSERT INTO employ (employee_id, employee_name, department_id) VALUES (103, 'Charlie', 1);

CREATE OR REPLACE PROCEDURE transfer_employee (
    p_employee_id IN NUMBER,
    p_new_department_id IN NUMBER
) IS
    v_old_department_id NUMBER;
BEGIN
    -- Find the current department of the employee
    SELECT department_id INTO v_old_department_id
    FROM employ
    WHERE employee_id = p_employee_id;

    -- Update the employee's department
    UPDATE employ
    SET department_id = p_new_department_id
    WHERE employee_id = p_employee_id;

    -- Optionally, you can log the department change or handle exceptions here
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_employee_id || ' moved from department ' || v_old_department_id || ' to department ' || p_new_department_id);
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee with ID ' || p_employee_id || ' does not exist.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        ROLLBACK;
END transfer_employee;
/

Create Table list

Create Button

Create process where button

Declare The Procedure under the PL/SQL section

Point Section Select After Submit

Identify the Button

📌 Final Thoughts

Using stored procedures in Oracle APEX not only keeps your application clean and secure but also enables modular business logic reuse. Try expanding this example with validations, logs, or sending email notifications.

✅ We hope these questions helped reinforce your Oracle APEX knowledge! Got more APEX questions or suggestions? Drop a comment below or check out more tutorials at Techtweet.xyz.