The goal of this post is to help students, job seekers, and professionals practice PL/SQL coding in a structured way and master problem-solving skills for exams, interviews, and real-world database projects.
Exercise 1 – Hello PL/SQL
Write a block that prints ‘Hello, PL/SQL!’ using DBMS_OUTPUT.
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;
/Exercise 2 – Simple Addition
Declare two variables a and b, assign values, and print their sum.
DECLARE
a NUMBER := 10; -- first variable
b NUMBER := 20; -- second variable
sum_result NUMBER; -- variable to hold the sum
BEGIN
sum_result := a + b; -- perform addition
DBMS_OUTPUT.PUT_LINE('The sum of ' || a || ' and ' || b || ' is: ' || sum_result);
END;
/Exercise 3 – IF Statement
Check if a number is positive or negative and print result.
DECLARE
num NUMBER := -15; -- change the value to test
BEGIN
IF num > 0 THEN
DBMS_OUTPUT.PUT_LINE(num || ' is Positive');
ELSIF num < 0 THEN
DBMS_OUTPUT.PUT_LINE(num || ' is Negative');
ELSE
DBMS_OUTPUT.PUT_LINE('The number is Zero');
END IF;
END;
/Exercise 4 – Multiplication Table
Print the multiplication table of 5 using a LOOP.
DECLARE
num NUMBER := 5; -- number for which table is printed
BEGIN
FOR i IN 1..7 LOOP
DBMS_OUTPUT.PUT_LINE(num || ' x ' || i || ' = ' || (num * i));
END LOOP;
END;
/Exercise 5 – Factorial
Write a PL/SQL program to calculate factorial of 5.
DECLARE
n NUMBER := 5; -- number to calculate factorial
factorial NUMBER := 1; -- variable to store factorial
BEGIN
FOR i IN 1..n LOOP
factorial := factorial * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial of ' || n || ' is: ' || factorial);
END;
/Exercise 6 – Prime Check
Check if a given number is prime.
DECLARE
num NUMBER := 17; -- number to check
is_prime BOOLEAN := TRUE;
BEGIN
IF num <= 1 THEN
is_prime := FALSE;
ELSE
FOR i IN 2..TRUNC(SQRT(num)) LOOP
IF MOD(num, i) = 0 THEN
is_prime := FALSE;
EXIT; -- exit loop if a divisor is found
END IF;
END LOOP;
END IF;
IF is_prime THEN
DBMS_OUTPUT.PUT_LINE(num || ' is a Prime number');
ELSE
DBMS_OUTPUT.PUT_LINE(num || ' is not a Prime number');
END IF;
END;
/Exercise 7 – Procedures
Write a procedure that accepts a number and prints whether it is odd or even.
SET SERVEROUTPUT ON;— Create the procedure
CREATE OR REPLACE PROCEDURE check_odd_even(p_num IN NUMBER) IS
BEGIN
IF MOD(p_num, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(p_num || ' is Even');
ELSE
DBMS_OUTPUT.PUT_LINE(p_num || ' is Odd');
END IF;
END;
/— Call the procedure
BEGIN
check_odd_even(15); -- You can change this number to test
check_odd_even(24);
END;
/Exercise 8 – Functions
Write a function that returns the square of a number.
CREATE OR REPLACE FUNCTION square_num(p_number IN NUMBER)
RETURN NUMBER
AS
BEGIN
RETURN p_number * p_number;
END;
/DECLARE
v_result NUMBER;
BEGIN
v_result := square_num(7);
DBMS_OUTPUT.PUT_LINE('Square of 7 is: ' || v_result);
END;
/Exercise 9 – Cursor Example
Use an explicit cursor to fetch and print employee names.
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
salary NUMBER(10,2),
dept_id NUMBER
);INSERT INTO employees (emp_id, name, salary, dept_id)
VALUES (1, 'Alice Johnson', 75000, 10);
INSERT INTO employees (emp_id, name, salary, dept_id)
VALUES (2, 'Bob Smith', 60000, 20);
INSERT INTO employees (emp_id, name, salary, dept_id)
VALUES (3, 'Charlie Brown', 55000, 20);
INSERT INTO employees (emp_id, name, salary, dept_id)
VALUES (4, 'Diana Prince', 50000, 30);
INSERT INTO employees (emp_id, name, salary, dept_id)
VALUES (5, 'Ethan Hunt', 80000, 10);DECLARE
-- Declare cursor
CURSOR emp_cur IS
SELECT name
FROM employees;
-- Variable to hold fetched value
v_name employees.name%TYPE;
BEGIN
-- Open the cursor
OPEN emp_cur;
LOOP
-- Fetch into variable
FETCH emp_cur INTO v_name;
EXIT WHEN emp_cur%NOTFOUND;
-- Print employee name
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
-- Close the cursor
CLOSE emp_cur;
END;
/Exercise 10 – Exception Handling
Write code that divides two numbers and handles divide-by-zero.
DECLARE
num1 NUMBER := 20;
num2 NUMBER := 0; -- change to a nonzero number to test normal case
result NUMBER;
BEGIN
BEGIN
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero!');
END;
END;
/Exercise 11 – User-defined Exception
Raise and handle a custom exception when salary < 1000.
DECLARE
v_salary employees.salary%TYPE := 800; -- test with less than 1000
-- Declare custom exception
ex_low_salary EXCEPTION;
BEGIN
-- Check condition
IF v_salary < 1000 THEN
RAISE ex_low_salary; -- raise exception
END IF;
DBMS_OUTPUT.PUT_LINE('Salary is acceptable: ' || v_salary);
EXCEPTION
WHEN ex_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be less than 1000! Entered salary = ' || v_salary);
END;
/Exercise 12 – Nested Blocks
Create a block inside another, handling exceptions separately.
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 0;
result NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Outer Block Started ---');
-- Inner block
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Inner Block Started ---');
-- This will cause divide by zero
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
DBMS_OUTPUT.PUT_LINE('--- Inner Block Ended ---');
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Inner Block Error: Cannot divide by zero!');
END;
-- Continue outer block
DBMS_OUTPUT.PUT_LINE('--- Outer Block Continues ---');
-- Simulate another error
RAISE VALUE_ERROR; -- for demonstration
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Outer Block Error: Invalid numeric operation!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Outer Block Error: ' || SQLERRM);
END;
/Exercise 13 – Packages
Create a package with a procedure and function for arithmetic operations.
————Package Specification———————-
CREATE OR REPLACE PACKAGE arithmetic_pkg AS
-- Function to add two numbers
FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER;
-- Function to subtract two numbers
FUNCTION subtract_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER;
-- Function to multiply two numbers
FUNCTION multiply_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER;
-- Procedure to divide two numbers and print result
PROCEDURE divide_numbers(p_num1 NUMBER, p_num2 NUMBER);
END arithmetic_pkg;
/—————-Package Body—————
CREATE OR REPLACE PACKAGE BODY arithmetic_pkg AS
FUNCTION add_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_num1 + p_num2;
END add_numbers;
FUNCTION subtract_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_num1 - p_num2;
END subtract_numbers;
FUNCTION multiply_numbers(p_num1 NUMBER, p_num2 NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_num1 * p_num2;
END multiply_numbers;
PROCEDURE divide_numbers(p_num1 NUMBER, p_num2 NUMBER) IS
v_result NUMBER;
BEGIN
v_result := p_num1 / p_num2;
DBMS_OUTPUT.PUT_LINE('Division Result: ' || v_result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero!');
END divide_numbers;
END arithmetic_pkg;
/——————Test the Package———————-
SET SERVEROUTPUT ON;DECLARE
v_result NUMBER;
BEGIN
-- Using functions
v_result := arithmetic_pkg.add_numbers(10, 5);
DBMS_OUTPUT.PUT_LINE('Addition: ' || v_result);
v_result := arithmetic_pkg.subtract_numbers(10, 5);
DBMS_OUTPUT.PUT_LINE('Subtraction: ' || v_result);
v_result := arithmetic_pkg.multiply_numbers(10, 5);
DBMS_OUTPUT.PUT_LINE('Multiplication: ' || v_result);
-- Using procedure
arithmetic_pkg.divide_numbers(10, 0); -- will trigger exception
END;
/Exercise 14 – Overloading
Overload a function to calculate area of circle and rectangle.
—————-Create a Package Specification———————
CREATE OR REPLACE PACKAGE area_pkg AS
-- Area of circle (radius)
FUNCTION calc_area(p_radius NUMBER) RETURN NUMBER;
-- Area of rectangle (length, width)
FUNCTION calc_area(p_length NUMBER, p_width NUMBER) RETURN NUMBER;
END area_pkg;
/———————Package Body————————
CREATE OR REPLACE PACKAGE BODY area_pkg AS
-- Area of circle
FUNCTION calc_area(p_radius NUMBER) RETURN NUMBER IS
BEGIN
RETURN 3.14159 * p_radius * p_radius;
END calc_area;
-- Area of rectangle
FUNCTION calc_area(p_length NUMBER, p_width NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_length * p_width;
END calc_area;
END area_pkg;
/—————–Test the Overloaded Functions——————–
SET SERVEROUTPUT ON;DECLARE
v_circle_area NUMBER;
v_rectangle_area NUMBER;
BEGIN
-- Circle with radius 5
v_circle_area := area_pkg.calc_area(5);
DBMS_OUTPUT.PUT_LINE('Area of Circle: ' || v_circle_area);
-- Rectangle with length 10 and width 4
v_rectangle_area := area_pkg.calc_area(10, 4);
DBMS_OUTPUT.PUT_LINE('Area of Rectangle: ' || v_rectangle_area);
END;
/Exercise 15 – Triggers
Create a trigger that logs inserts on the employee table.
————–Create a Log Table ———————–
CREATE TABLE employee_log (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
emp_id NUMBER,
name VARCHAR2(100),
salary NUMBER(10,2),
dept_id NUMBER,
inserted_on DATE DEFAULT SYSDATE
);——————Create the Trigger——————
CREATE OR REPLACE TRIGGER trg_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log (emp_id, name, salary, dept_id)
VALUES (:NEW.emp_id, :NEW.name, :NEW.salary, :NEW.dept_id);
END;
/—————-Test the Trigger—————–
— Insert a new employee
INSERT INTO employees (emp_id, name, salary, dept_id)
VALUES (6, 'Frank Castle', 70000, 40);
COMMIT;— Check the log table
SELECT * FROM employee_log;Exercise 16 – Bulk Collect
Fetch multiple rows into a collection using BULK COLLECT.
SET SERVEROUTPUT ON;DECLARE
-- Declare a collection (nested table) to hold employee names
TYPE emp_name_tbl IS TABLE OF employees.name%TYPE;
v_emp_names emp_name_tbl;
BEGIN
-- Fetch all employee names into the collection
SELECT name
BULK COLLECT INTO v_emp_names
FROM employees
WHERE salary > 50000; -- example condition
-- Loop through the collection and print names
FOR i IN 1 .. v_emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_names(i));
END LOOP;
END;
/Exercise 17 – FORALL
Use FORALL to insert multiple records into a table efficiently.
CREATE TABLE employees_bulk (
emp_id NUMBER,
name VARCHAR2(50),
salary NUMBER
);set serveroutput onDECLARE
-- Define PL/SQL collection types
TYPE emp_id_tab IS TABLE OF NUMBER;
TYPE name_tab IS TABLE OF VARCHAR2(50);
TYPE salary_tab IS TABLE OF NUMBER;
-- Declare and initialize collections
v_emp_ids emp_id_tab := emp_id_tab(201, 202, 203, 204,205);
v_names name_tab := name_tab('Rahim', 'Karim', 'Jasim', 'Hasan', 'Kuddus');
v_salaries salary_tab := salary_tab(3000, 3500, 2800, 4000, 3200);
BEGIN
-- Bulk insert using FORALL
FORALL i IN v_emp_ids.FIRST .. v_emp_ids.LAST
INSERT INTO employees_bulk (emp_id, name, salary)
VALUES (v_emp_ids(i), v_names(i), v_salaries(i));
DBMS_OUTPUT.PUT_LINE('Bulk insert completed successfully!');
END;
/Exercise 18 – Record Types
Create and use a RECORD type for student info.
DECLARE
-- Define a RECORD type for student info
TYPE student_rec IS RECORD (
student_id NUMBER,
student_name VARCHAR2(50),
age NUMBER,
dept VARCHAR2(30)
);
-- Declare a variable of that record type
v_student student_rec;
BEGIN
-- Assign values to the record fields
v_student.student_id := 101;
v_student.student_name := 'Rahim';
v_student.age := 21;
v_student.dept := 'Computer Science';
-- Print values using DBMS_OUTPUT
DBMS_OUTPUT.PUT_LINE('Student ID : ' || v_student.student_id);
DBMS_OUTPUT.PUT_LINE('Name : ' || v_student.student_name);
DBMS_OUTPUT.PUT_LINE('Age : ' || v_student.age);
DBMS_OUTPUT.PUT_LINE('Department : ' || v_student.dept);
END;
/Exercise 19 – Object Types
Define an object type ‘Car’ with attributes and methods.
CREATE OR REPLACE TYPE Car AS OBJECT (
-- Attributes
car_id NUMBER,
brand VARCHAR2(50),
model VARCHAR2(50),
price NUMBER,
-- Member methods
MEMBER PROCEDURE display_info,
MEMBER FUNCTION apply_discount(p_percent NUMBER) RETURN NUMBER
);
/CREATE OR REPLACE TYPE BODY Car AS
-- Procedure to display car information
MEMBER PROCEDURE display_info IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Car ID : ' || car_id);
DBMS_OUTPUT.PUT_LINE('Brand : ' || brand);
DBMS_OUTPUT.PUT_LINE('Model : ' || model);
DBMS_OUTPUT.PUT_LINE('Price : ' || price);
END display_info;
-- Function to calculate discounted price
MEMBER FUNCTION apply_discount(p_percent NUMBER) RETURN NUMBER IS
BEGIN
RETURN price - (price * (p_percent / 100));
END apply_discount;
END;
/DECLARE
v_car Car;
v_new_price NUMBER;
BEGIN
-- Initialize object
v_car := Car(1, 'Toyota', 'Corolla', 20000);
-- Call method to display info
v_car.display_info;
-- Apply discount
v_new_price := v_car.apply_discount(10);
DBMS_OUTPUT.PUT_LINE('Discounted Price : ' || v_new_price);
END;
/Exercise 20 – Nested Tables
Store multiple phone numbers for an employee in a nested table.
— Create a collection type for phone numbers
CREATE OR REPLACE TYPE phone_list AS TABLE OF VARCHAR2(15);
/CREATE TABLE employees_nt (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
phones phone_list
) NESTED TABLE phones STORE AS phones_nt_tab;
INSERT INTO employees_nt VALUES (
1,
'Rahim',
phone_list('01711111111', '01822222222', '01933333333')
);
INSERT INTO employees_nt VALUES (
2,
'Karim',
phone_list('01544444444')
);— Normal query (phones not expanded)
SELECT * FROM employees_nt;— Expand phone numbers using TABLE()
SELECT e.emp_id, e.emp_name, p.COLUMN_VALUE AS phone
FROM employees_nt e,
TABLE(e.phones) p;Exercise 21 – VARRAY
Create a VARRAY to hold top 5 scores of a student.— Create a VARRAY type that can hold max 5 scores
CREATE OR REPLACE TYPE score_array AS VARRAY(5) OF NUMBER;
/CREATE TABLE students_varray (
student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(50),
scores score_array
);Exercise 22 – Autonomous Transactions
Write a procedure that logs audit records using PRAGMA AUTONOMOUS_TRANSACTION.
CREATE TABLE audit_log (
log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
log_date DATE DEFAULT SYSDATE,
user_name VARCHAR2(50),
action VARCHAR2(200),
module VARCHAR2(100),
details VARCHAR2(4000)
);|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE OR REPLACE PROCEDURE log_audit ( p_user_name IN VARCHAR2, p_action IN VARCHAR2, p_module IN VARCHAR2, p_details IN VARCHAR2 DEFAULT NULL ) IS PRAGMA AUTONOMOUS_TRANSACTION; -- runs independently from caller transaction BEGIN INSERT INTO audit_log (user_name, action, module, details) VALUES (p_user_name, p_action, p_module, p_details); COMMIT; -- commit independently EXCEPTION WHEN OTHERS THEN -- rollback only inside this autonomous transaction ROLLBACK; DBMS_OUTPUT.PUT_LINE('Error logging audit record: ' || SQLERRM); END; / |
|
1 2 3 |
set serveroutput on |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
BEGIN -- User John updating a customer record log_audit( p_user_name => 'JOHN', p_action => 'UPDATE', p_module => 'CUSTOMER_MAINTENANCE', p_details => 'Changed address for customer ID 101' ); -- User Alice inserting a new order log_audit( p_user_name => 'ALICE', p_action => 'INSERT', p_module => 'ORDER_ENTRY', p_details => 'Created new order ID 5001' ); -- User Mark deleting a product log_audit( p_user_name => 'MARK', p_action => 'DELETE', p_module => 'PRODUCT_CATALOG', p_details => 'Removed product ID P202' ); -- User Admin logging in log_audit( p_user_name => 'ADMIN', p_action => 'LOGIN', p_module => 'SECURITY', p_details => 'Admin login successful' ); END; / |
Exercise 23 – Materialized Views with PL/SQL
Refresh and query a materialized view inside PL/SQL.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE orders ( order_id NUMBER, customer_id NUMBER, order_total NUMBER ); |
— Insert some sample data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
INSERT INTO orders VALUES (2, 101, 1000); INSERT INTO orders VALUES (3, 203, 2200); INSERT INTO orders VALUES (4, 204, 300); COMMIT; CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT customer_id, SUM(order_total) total_sales FROM orders GROUP BY customer_id; SELECT * FROM sales_mv; |
Exercise 24 – DBMS_SCHEDULER
Schedule a PL/SQL job that runs daily at midnight.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'refresh_sales_mv_job', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_MVIEW.REFRESH(''SALES_MV'', ''C''); END;', start_date => TRUNC(SYSDATE) + 1, -- start from tomorrow midnight repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0', enabled => TRUE, comments => 'Daily refresh of SALES_MV at midnight' ); END; / set serveroutput on SELECT job_name, enabled, state, last_start_date, next_run_date FROM user_scheduler_jobs WHERE job_name = 'REFRESH_SALES_MV_JOB'; BEGIN DBMS_SCHEDULER.RUN_JOB('REFRESH_SALES_MV_JOB'); END; / |
Exercise 25 – Dynamic SQL
Execute a dynamically built query using EXECUTE IMMEDIATE.
- Simple Dynamic SELECT INTO Variable
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE v_table_name VARCHAR2(30) := 'ORDERS'; v_count NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count; DBMS_OUTPUT.PUT_LINE('Total rows in ' || v_table_name || ' = ' || v_count); END; / |
- Dynamic DML (INSERT/UPDATE/DELETE)
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN EXECUTE IMMEDIATE 'UPDATE orders SET order_total = order_total * 1.1 WHERE customer_id = 101'; DBMS_OUTPUT.PUT_LINE('Orders updated for customer 101'); END; / |
- Dynamic Query with Parameters (Using Bind Variables)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE v_sql VARCHAR2(2000); v_total NUMBER; v_cust NUMBER := 101; BEGIN v_sql := 'SELECT SUM(order_total) FROM orders WHERE customer_id = :id'; EXECUTE IMMEDIATE v_sql INTO v_total USING v_cust; DBMS_OUTPUT.PUT_LINE('Total sales for customer ' || v_cust || ' = ' || v_total); END; / |
- Returning Multiple Rows with BULK COLLECT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DECLARE TYPE t_tab IS TABLE OF orders%ROWTYPE; v_rows t_tab; v_sql VARCHAR2(2000); BEGIN v_sql := 'SELECT * FROM orders WHERE order_total > :min_total'; EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_rows USING 500; FOR i IN 1 .. v_rows.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Order ID: ' || v_rows(i).order_id || ', Customer: ' || v_rows(i).customer_id || ', Total: ' || v_rows(i).order_total); END LOOP; END; / |
Exercise 26 – Bind Variables
Use bind variables with dynamic SQL for better performance.
1. Dynamic SELECT with a Single Bind
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE v_sql VARCHAR2(200); v_total NUMBER; v_cust NUMBER := 101; BEGIN v_sql := 'SELECT SUM(order_total) FROM orders WHERE customer_id = :cust_id'; EXECUTE IMMEDIATE v_sql INTO v_total USING v_cust; DBMS_OUTPUT.PUT_LINE('Customer ' || v_cust || ' total = ' || v_total); END; / |
2. Dynamic SELECT with Multiple Binds
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE v_sql VARCHAR2(200); v_min NUMBER := 500; v_max NUMBER := 2000; v_count NUMBER; BEGIN v_sql := 'SELECT COUNT(*) FROM orders WHERE order_total BETWEEN :low AND :high'; EXECUTE IMMEDIATE v_sql INTO v_count USING v_min, v_max; DBMS_OUTPUT.PUT_LINE('Orders between ' || v_min || ' and ' || v_max || ' = ' || v_count); END; / |
3. Dynamic DML with Binds
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
DECLARE v_sql VARCHAR2(200); v_min NUMBER := 500; v_max NUMBER := 2000; v_count NUMBER; BEGIN v_sql := 'SELECT COUNT(*) FROM orders WHERE order_total BETWEEN :low AND :high'; EXECUTE IMMEDIATE v_sql INTO v_count USING v_min, v_max; DBMS_OUTPUT.PUT_LINE('Orders between ' || v_min || ' and ' || v_max || ' = ' || v_count); END; / |
4. Fetch Multiple Rows with Binds (BULK COLLECT)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
DECLARE TYPE t_orders IS TABLE OF orders%ROWTYPE; v_rows t_orders; v_sql VARCHAR2(200); v_amt NUMBER := 1000; BEGIN v_sql := 'SELECT * FROM orders WHERE order_total > :min_total'; EXECUTE IMMEDIATE v_sql BULK COLLECT INTO v_rows USING v_amt; FOR i IN 1 .. v_rows.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Order ' || v_rows(i).order_id || ' → Total: ' || v_rows(i).order_total); END LOOP; END; / |
Exercise 27 – Native Compilation
Compile a PL/SQL unit natively for performance boost.
- One-off: compile a specific unit as native
— Procedure / Function / Package / Trigger
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE = NATIVE; ALTER FUNCTION my_func COMPILE PLSQL_CODE_TYPE = NATIVE; ALTER PACKAGE my_pack COMPILE PLSQL_CODE_TYPE = NATIVE; -- spec only ALTER PACKAGE my_pack COMPILE BODY PLSQL_CODE_TYPE = NATIVE; -- body ALTER TRIGGER my_trg COMPILE PLSQL_CODE_TYPE = NATIVE; Revert to interpreted: ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE = INTERPRETED; |
- Session/system default (affects new compilations)
— Session scope (your current connection)
ALTER SESSION SET plsql_code_type = NATIVE;
ALTER SESSION SET plsql_optimize_level = 3;
— Instance/system scope (needs privileges; affects future compilations)
ALTER SYSTEM SET plsql_code_type = NATIVE SCOPE=BOTH;
ALTER SYSTEM SET plsql_optimize_level = 3 SCOPE=BOTH;
- Force recompilation so existing units pick up the setting
— Recompile one schema’s invalids
BEGIN
UTL_RECOMP.recomp_serial(schema => USER); — or a specific schema name
END;
/
— Or recompile a specific object after changing the session setting
ALTER PROCEDURE my_proc COMPILE; — will use current session’s plsql_code_type
- Verify what actually happened
— Check per-object settings (your schema)
SELECT name, type, plsql_code_type, plsql_optimize_level
FROM user_plsql_object_settings
ORDER BY name;
— Check dictionary view that shows what’s in effect
SELECT object_name, object_type, plsql_code_type, plsql_optimize_level
FROM user_plsql_object_settings
WHERE name = ‘MY_PROC’;
- Measure quickly
SET SERVEROUTPUT ON
DECLARE
t1 TIMESTAMP; t2 TIMESTAMP;
BEGIN
t1 := SYSTIMESTAMP;
— call hot code many times
FOR i IN 1..100000 LOOP
my_proc; — or my_func(…)
END LOOP;
t2 := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE(‘Elapsed: ‘ || TO_CHAR(t2 – t1));
END;
/
- Notes & gotchas
· Modern Oracle (11gR2+): native compilation is integrated—no external C compiler or native library directories required.
· Native helps most when code is CPU-bound with tight loops and minimal SQL context switching. If your time is in SQL, optimize SQL first.
· Keep PLSQL_WARNINGS on during development:
ALTER SESSION SET plsql_warnings = ‘ENABLE:ALL’;
· If you toggle plsql_code_type at the session level, you must recompile units in that session to change them to native/interpreted.
Exercise 28 – Distributed Transactions
Write a block that updates two remote databases in a single transaction.
DECLARE
v_errmsg VARCHAR2(4000);
BEGIN
— Update remote database #1
UPDATE employees@db_link1
SET salary = salary * 1.10
WHERE department_id = 50;
— Update remote database #2
UPDATE employees@db_link2
SET salary = salary * 1.05
WHERE department_id = 60;
— If all updates succeed, commit both together
COMMIT;
EXCEPTION
WHEN OTHERS THEN
— Rollback all updates if any error occurs
ROLLBACK;
v_errmsg := SQLERRM;
DBMS_OUTPUT.PUT_LINE(‘Error occurred: ‘ || v_errmsg);
END;
/
Exercise 29 – Parallel Execution
Invoke DBMS_PARALLEL_EXECUTE to process large data in chunks.
Suppose we have a table:
CREATE TABLE big_sales (
sale_id NUMBER PRIMARY KEY,
amount NUMBER,
processed CHAR(1) DEFAULT ‘N’
);
We want to update millions of rows, setting processed = 'Y' — but in parallel chunks for performance.
1.Create a Task
BEGIN
DBMS_PARALLEL_EXECUTE.CREATE_TASK(‘process_sales_task’);
END;
/
2.Divide Table into Chunks by Rowid
BEGIN
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
task_name => ‘process_sales_task’,
table_owner => ‘HR’,
table_name => ‘BIG_SALES’,
by_row => TRUE,
chunk_size => 10000 — rows per chunk
);
END;
/
3. Define SQL to Run on Each Chunk
BEGIN
DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => ‘process_sales_task’,
sql_stmt => ‘UPDATE big_sales
SET processed = ”Y”
WHERE ROWID BETWEEN :start_id AND :end_id’,
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4 — run 4 workers in parallel
);
END;
/
4.Monitor Task Progress
SELECT task_name, status, start_time, end_time
FROM user_parallel_execute_tasks
WHERE task_name = ‘process_sales_task’;
5.Drop the Task After Completion
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK(‘process_sales_task’);
END;
/
Exercise 30 – Advanced Optimizations
Profile PL/SQL code using DBMS_PROFILER and optimize it.
🔹 Step 1: Setup for Profiling
— Create profiler tables (one-time setup)
@$ORACLE_HOME/rdbms/admin/profload.sql
🔹 Step 2: Example PL/SQL Code (Before Optimization)
CREATE OR REPLACE PROCEDURE slow_sum IS
v_total NUMBER := 0;
BEGIN
FOR i IN 1..100000 LOOP
v_total := v_total + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total: ‘ || v_total);
END;
/
🔹 Step 3: Profile the Code
— Start profiling
EXEC DBMS_PROFILER.START_PROFILER(‘Profiling slow_sum procedure’);
— Run the procedure
EXEC slow_sum;
— Stop profiling
EXEC DBMS_PROFILER.STOP_PROFILER;
🔹 Step 4: Analyze Profile Data
— Find runs
SELECT runid, run_comment, run_date
FROM plsql_profiler_runs
ORDER BY runid DESC;
— Check time spent in each unit
SELECT u.unit_name, d.total_occur, d.total_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.unit_number = d.unit_number
WHERE u.unit_name = ‘SLOW_SUM’;
🔹 Step 5: Optimize the Code
CREATE OR REPLACE PROCEDURE fast_sum IS
v_total NUMBER := 0;
v_n NUMBER := 100000;
BEGIN
— Formula for sum of first N natural numbers = n(n+1)/2
v_total := v_n * (v_n + 1) / 2;
DBMS_OUTPUT.PUT_LINE(‘Total: ‘ || v_total);
END;
/
🔹 Step 6: Re-Profile the Optimized Code
EXEC DBMS_PROFILER.START_PROFILER(‘Profiling fast_sum procedure’);
EXEC fast_sum;
EXEC DBMS_PROFILER.STOP_PROFILER;
Exercise 31 – Polymorphic Table Functions (PTFs)
Goal: Create functions that adapt to input table structures.
CREATE OR REPLACE PACKAGE ptf_pkg AS
FUNCTION describe(tab IN OUT DBMS_TF.table_t) RETURN DBMS_TF.describe_t;
PROCEDURE fetch_rows;
END ptf_pkg;
/
— Example is simplified — PTFs are advanced features for dynamic query shaping
Try:
Implement a PTF that masks sensitive columns dynamically.
Exercise 32 – Secure Coding with Code Signing
Goal: Sign PL/SQL code for tamper detection.
— Create wallet and certificate outside PL/SQL
— Example command (done in OS):
— orapki wallet create
— In PL/SQL:
ALTER PROCEDURE sensitive_proc SIGN;
Try:
- Test signature verification after altering code.
Exercise 33 – Application Context for Multi-Tenant Security
Goal: Dynamically filter data per tenant/user.
BEGIN
DBMS_SESSION.SET_CONTEXT(‘app_ctx’, ‘tenant_id’, ‘T1001’);
END;
/
CREATE OR REPLACE FUNCTION tenant_policy_fn (
schema_name VARCHAR2,
table_name VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
RETURN ‘tenant_id = SYS_CONTEXT(”app_ctx”, ”tenant_id”)’;
END;
Try:
- Switch tenant context and see filtered results.
Exercise 34 – Real-Time Streaming via Advanced Queuing
Goal: Use AQ to send messages between sessions.
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => ‘message_queue_table’,
queue_payload_type => ‘SYS.AQ$_JMS_TEXT_MESSAGE’
);
DBMS_AQADM.CREATE_QUEUE(
queue_name => ‘message_queue’,
queue_table => ‘message_queue_table’
);
DBMS_AQADM.START_QUEUE(queue_name => ‘message_queue’);
END;
Try:
- Publish and consume messages in different sessions.
Exercise 35 – Sharded Database PL/SQL
Goal: Execute PL/SQL in a sharded environment.
— Requires Oracle Sharding setup
— Example: Run on specific shard
EXECUTE IMMEDIATE ‘ALTER SESSION SET CONTAINER = shard1’;
Try:
- Implement cross-shard data aggregation procedure.
Exercise 36 – PL/SQL Code Generation
Goal: Write PL/SQL that writes PL/SQL.
DECLARE
v_code VARCHAR2(4000);
BEGIN
v_code := ‘BEGIN DBMS_OUTPUT.PUT_LINE(”Generated Code Execution”); END;’;
EXECUTE IMMEDIATE v_code;
END;
Try:
- Generate a procedure dynamically from metadata tables.
