Press "Enter" to skip to content

Oracle PL/SQL: Dynamic SQL Example Using Execute Immediate

In this tutorial, I am giving an Oracle PL/SQL program example to demonstrate Dynamic SQL using EXECUTE IMMEDIATE statement.

Oracle PL/SQL EXECUTE IMMEDIATE Dynamic SQL Example

The following PL/SQL procedure will update the record in the Employees table for an employee ID if a value is different from the actual value.

The Update statement will be prepared dynamically using a string, and then it will be executed using the EXECUTE IMMEDIATE statement. The SQL statement will be made only for the parameters passed and having new values.

For example, if you want to update the first_name and the salary of the employee, then pass the values for parameter i_id, first_name, and i_salary, rest parameter can be null. The update statement will be generated for these columns only.

CREATE OR REPLACE PROCEDURE update_emp_rec (
i_id               IN employees.employee_id%TYPE,
i_first            IN employees.first_name%TYPE,
i_last             IN employees.last_name%TYPE,
i_email            IN employees.email%TYPE,
i_phone            IN employees.phone_number%TYPE,
i_job              IN employees.job_id%TYPE,
i_salary           IN employees.salary%TYPE,
i_commission_pct   IN employees.commission_pct%TYPE,
i_manager_id       IN employees.manager_id%TYPE,
i_department_id    IN employees.department_id%TYPE)
AS
emp_upd_rec   employees%ROWTYPE;
sql_string    VARCHAR2 (1000);
set_count     NUMBER := 0;
BEGIN
SELECT *
INTO emp_upd_rec
FROM employees
WHERE employee_id = i_id;
sql_string := 'UPDATE EMPLOYEES SET ';
IF i_first != emp_upd_rec.first_name
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', FIRST_NAME =''' || i_first || '''';
ELSE
sql_string := sql_string || ' FIRST_NAME =''' || i_first || '''';
set_count := set_count + 1;
END IF;
END IF;
IF i_last != emp_upd_rec.last_name
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', LAST_NAME =''' || i_last || '''';
ELSE
sql_string := sql_string || ' LAST_NAME =''' || i_last || '''';
set_count := set_count + 1;
END IF;
END IF;
IF UPPER (i_email) != emp_upd_rec.email
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', EMAIL =''' || UPPER (i_email) || '''';
ELSE
sql_string := sql_string || ' EMAIL =''' || UPPER (i_email) || '''';
set_count := set_count + 1;
END IF;
END IF;
IF UPPER (i_phone) != emp_upd_rec.phone_number
THEN
IF set_count > 0
THEN
sql_string :=
sql_string || ', PHONE_NUMBER =''' || UPPER (i_phone) || '''';
ELSE
sql_string :=
sql_string || ' PHONE_NUMBER =''' || UPPER (i_phone) || '''';
set_count := set_count + 1;
END IF;
END IF;
IF i_job != emp_upd_rec.job_id
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', JOB_ID =''' || i_job || '''';
ELSE
sql_string := sql_string || ' JOB_ID =''' || i_job || '''';
set_count := set_count + 1;
END IF;
END IF;
IF i_salary != emp_upd_rec.salary
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', SALARY =' || i_salary;
ELSE
sql_string := sql_string || ' SALARY =' || i_salary;
set_count := set_count + 1;
END IF;
END IF;
IF i_commission_pct != emp_upd_rec.commission_pct
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', COMMISSION_PCT =' || i_commission_pct;
ELSE
sql_string := sql_string || ' COMMISSION_PCT =' || i_commission_pct;
set_count := set_count + 1;
END IF;
END IF;
IF i_manager_id != emp_upd_rec.manager_id
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', MANAGER_ID =' || i_manager_id;
ELSE
sql_string := sql_string || ' MANAGER_ID =' || i_manager_id;
set_count := set_count + 1;
END IF;
END IF;
IF i_department_id != emp_upd_rec.department_id
THEN
IF set_count > 0
THEN
sql_string := sql_string || ', DEPARTMENT_ID =' || i_department_id;
ELSE
sql_string := sql_string || ' DEPARTMENT_ID =' || i_department_id;
set_count := set_count + 1;
END IF;
END IF;
sql_string := sql_string || ' WHERE employee_id = ' || i_id;
IF set_count > 0
THEN
DBMS_OUTPUT.put_Line (sql_string);
EXECUTE IMMEDIATE sql_string;
ELSE
DBMS_OUTPUT.PUT_LINE (
'No update needed, ' || 'all fields match original values');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('No matching employee found');
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(   'Data entry error has occurred, '
|| 'please check values and try again'
|| sql_string);
END;
/

Run The Procedure

The following is the PL/SQL code, to execute the above procedure to update the first_name and the salary for employee ID 199.

SET SERVEROUTPUT ON;
DECLARE
I_ID               NUMBER;
I_FIRST            VARCHAR2 (20);
I_LAST             VARCHAR2 (25);
I_EMAIL            VARCHAR2 (25);
I_PHONE            VARCHAR2 (20);
I_JOB              VARCHAR2 (10);
I_SALARY           NUMBER;
I_COMMISSION_PCT   NUMBER;
I_MANAGER_ID       NUMBER;
I_DEPARTMENT_ID    NUMBER;
BEGIN
I_ID := 199;
I_FIRST := 'Joseph';
I_LAST := NULL;
I_EMAIL := NULL;
I_PHONE := NULL;
I_JOB := NULL;
I_SALARY := 3099;
I_COMMISSION_PCT := NULL;
I_MANAGER_ID := NULL;
I_DEPARTMENT_ID := NULL;
UPDATE_EMP_REC (I_ID,
I_FIRST,
I_LAST,
I_EMAIL,
I_PHONE,
I_JOB,
I_SALARY,
I_COMMISSION_PCT,
I_MANAGER_ID,
I_DEPARTMENT_ID);
COMMIT;
END;
/

Output

UPDATE EMPLOYEES SET FIRST_NAME ='Joseph', SALARY =3099 WHERE employee_id = 199
PL/SQL procedure successfully completed.

See also:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Comments are closed.