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.
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
|
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
|
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 |
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.
|
1 2 3 |
SET SERVEROUTPUT ON; |
— Create the procedure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR REPLACE FUNCTION square_num(p_number IN NUMBER) RETURN NUMBER AS BEGIN RETURN p_number * p_number; END; / |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, name VARCHAR2(100) NOT NULL, salary NUMBER(10,2), dept_id NUMBER ); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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); |
|
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 |
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.
|
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 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.
|
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 |
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.
|
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 |
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———————-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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—————
|
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 |
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———————-
|
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 |
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———————
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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————————
|
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 |
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——————–
|
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 |
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 ———————–
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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——————
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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
|
1 2 3 4 5 6 7 |
INSERT INTO employees (emp_id, name, salary, dept_id) VALUES (6, 'Frank Castle', 70000, 40); COMMIT; |
— Check the log table
|
1 2 3 |
SELECT * FROM employee_log; |
Exercise 16 – Bulk Collect
Fetch multiple rows into a collection using BULK COLLECT.
|
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE employees_bulk ( emp_id NUMBER, name VARCHAR2(50), salary NUMBER ); |
|
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 |
DECLARE -- 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.
|
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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 ); / |
|
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 |
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; / |
|
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 |
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
|
1 2 3 4 5 |
CREATE OR REPLACE TYPE phone_list AS TABLE OF VARCHAR2(15); / |
|
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 |
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)
|
1 2 3 |
SELECT * FROM employees_nt; |
— Expand phone numbers using TABLE()
|
1 2 3 4 5 6 7 |
SELECT e.emp_id, e.emp_name, p.COLUMN_VALUE AS phone FROM employees_nt e, TABLE(e.phones) p; |
Exercise 21 – VARRAY
|
1 2 3 |
Create a VARRAY to hold top 5 scores of a student. |
— Create a VARRAY type that can hold max 5 scores
|
1 2 3 4 5 |
CREATE OR REPLACE TYPE score_array AS VARRAY(5) OF NUMBER; / |
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
I must say this article is extremely well written, insightful, and packed with valuable knowledge that shows the author’s deep expertise on the subject, and I truly appreciate the time and effort that has gone into creating such high-quality content because it is not only helpful but also inspiring for readers like me who are always looking for trustworthy resources online. Keep up the good work and write more. i am a follower.