ORACLE PL/SQL Fundamentals — Session 3

Overview

This course session provides a comprehensive, hands-on training in PL/SQL programming within Oracle SQL Developer, covering core concepts such as variable declaration, exception handling, dynamic SQL, conditional logic (CASE/DECODE), row types, nested blocks, transaction control (COMMIT/ROLLBACK), and error logging. The instructor guides participants through live debugging, code correction, and practical implementation of database operations, emphasizing robust programming practices, error recovery, and application-level validation using user-defined exceptions and raise_application_error. The session concludes with an introduction to nested blocks and sets the stage for the next topic: iteration and looping.

Topic (Timeline)

1. Environment Setup and Basic SQL Debugging [00:00:08 - 00:05:17]

The session begins with live troubleshooting of SQL syntax errors in Oracle SQL Developer, including removing unintended spaces in column names (e.g., “first name” → “first_name”), correcting line 6 syntax (replacing OR with ||), and verifying EMP_ID values. Participants are guided to use SET SERVEROUTPUT ON to display DBMS_OUTPUT results. Key focus: ensuring correct variable declaration (capital V vs. lowercase v), proper use of semicolons, and validating data existence in the EMPLOYEES table.

2. Exception Handling: NO_DATA_FOUND and TOO_MANY_ROWS [00:05:23 - 00:10:27]

The instructor introduces PL/SQL exception handling, focusing on two predefined exceptions: NO_DATA_FOUND (when SELECT INTO returns no rows) and TOO_MANY_ROWS (when multiple rows are returned). Participants implement exception blocks using DBMS_OUTPUT to display custom messages (e.g., “No employee found with given ID”). Emphasis is placed on the necessity of exception handling when using SELECT INTO, which requires exactly one result row.

3. Cursors and Bulk Data Processing [00:10:27 - 00:15:34]

The session transitions to cursor usage for iterating over multiple rows. Participants learn to declare, open, fetch, and close explicit cursors. The instructor demonstrates looping through cursor results using a WHILE loop with EXIT WHEN %NOTFOUND. Code is refined to include proper cursor syntax and output of employee salary and name. Participants are instructed to prepare data for cursor testing by inserting additional records.

4. Table Design and Foreign Key Constraints [00:15:34 - 00:20:06]

Participants create a DEPARTMENTS table and alter the EMPLOYEES table to add a DEPARTMENT_ID column. The instructor demonstrates adding a foreign key constraint (FK_DEPARTMENT) linking EMPLOYEES to DEPARTMENTS. Troubleshooting includes refreshing connections, reconnecting to the database, and verifying table visibility in SQL Developer’s object browser. Emphasis on data integrity and schema design.

5. Dynamic SQL with EXECUTE IMMEDIATE [00:20:06 - 00:35:47]

The instructor introduces dynamic SQL using EXECUTE IMMEDIATE. Participants learn to construct SQL statements as strings with bind variables (e.g., “SELECT first_name FROM employees WHERE emp_id = :1”). The USING clause binds runtime values (e.g., 101), and results are stored in variables via INTO. Live debugging shows how to handle cases where the ID does not exist, and participants verify results by modifying employee records (e.g., changing EMP_ID 101 to match a name).

6. Variable Population and Assignment Methods [00:35:47 - 00:46:26]

Core PL/SQL variable handling is covered: direct assignment (:=), SELECT INTO for database retrieval, and default initialization during declaration (e.g., emp_name VARCHAR2(50) := 'Unknown'). Participants implement salary calculations using expressions (e.g., new_salary := base_salary * 1.1). The instructor highlights that SELECT INTO must return exactly one row, reinforcing the need for exception handling.

7. ROWTYPE and Record Variables [00:46:26 - 00:58:29]

The ROWTYPE attribute is introduced to declare record variables that mirror the structure of a table row. Participants declare emp_record employees%ROWTYPE, use SELECT * INTO to populate it, and access individual fields via dot notation (e.g., emp_record.first_name, emp_record.salary). This enables efficient handling of entire rows without declaring individual variables.

8. Conditional Logic: CASE and DECODE [00:58:29 - 01:54:45]

The session covers conditional logic in SQL and PL/SQL. DECODE is demonstrated as a simple Oracle-specific alternative to CASE (e.g., DECODE(department_id, 10, 'HR', 20, 'Sales', 'Other')). The CASE statement is explored in both simple (value matching) and searched (Boolean condition) forms. Participants write queries to categorize salaries into “Low,” “Medium,” or “High” using CASE, with ELSE as fallback. Emphasis on proper syntax: CASE ... WHEN ... THEN ... ELSE ... END.

9. Exception Handling: User-Defined and Raise_Application_Error [01:54:45 - 02:24:25]

Participants learn to define custom exceptions (e.g., low_salary EXCEPTION), raise them with RAISE, and handle them in EXCEPTION blocks. The instructor demonstrates RAISE_APPLICATION_ERROR to generate custom error codes (within -20000 to -20999) and messages (e.g., “Salary below minimum required level”). This is applied in business logic validation (e.g., preventing negative withdrawals or underage access). The use of WHEN OTHERS to catch unanticipated errors is emphasized.

10. Error Logging and Internal Errors [02:24:25 - 02:11:40]

The session covers logging errors to a custom table using SQLCODE and SQLERRM to capture error numbers and messages. Participants learn to insert error details (code, message, timestamp) into an error_log table. Internal Oracle errors (ORA-00600, ORA-04031, ORA-01555, ORA-01000) are introduced as system-level issues requiring database administration, not application-level fixes. Best practices: use WHEN OTHERS to log, then re-raise or notify support.

11. Transaction Control: COMMIT and ROLLBACK [02:11:40 - 02:37:04]

The difference between COMMIT (permanently saving changes) and ROLLBACK (undoing uncommitted changes) is explained. Participants write code to insert and update records, then deliberately trigger an error (e.g., inserting NULL into a NOT NULL column) to test ROLLBACK. The session confirms that ROLLBACK reverts all changes since the last COMMIT, and both operations release locks. A commit is required to make schema changes (e.g., ALTER TABLE) permanent.

12. Nested Blocks and Variable Scope [02:37:04 - 02:45:07]

Nested PL/SQL blocks are introduced, with DECLARE-BEGIN-END structures within outer blocks. Participants observe variable scoping: outer block variables are accessible in inner blocks, but inner variables are local. Exception handling is demonstrated at both levels, showing how inner exceptions can be handled without affecting the outer block. Code structure, indentation, and comments are emphasized for readability.

13. Summary and Preview of Next Topic [02:45:07 - 03:49:38]

The instructor summarizes key concepts: variable declaration, exception handling, dynamic SQL, row types, transaction control, and nested blocks. A quick exam is announced for the next day. The session ends with a preview of the next topic: iteration and looping (e.g., FOR, WHILE loops). Participants are directed to review provided materials and ask questions via a shared Google Sheet.

Appendix

Key Principles

• SELECT INTO requires exactly one row; use exception handling (NO_DATA_FOUND, TOO_MANY_ROWS) to manage edge cases.
• Dynamic SQL (EXECUTE IMMEDIATE) enables runtime query construction with bind variables for security and flexibility.
• ROWTYPE allows a single variable to hold an entire table row, simplifying data access.
• CASE is more flexible than DECODE; use CASE for complex conditions, DECODE for simple value mapping.
• RAISE_APPLICATION_ERROR enforces business rules with custom error codes (-20000 to -20999) and messages.
• WHEN OTHERS should be used sparingly and always paired with logging (SQLCODE, SQLERRM) to avoid silent failures.
• COMMIT finalizes transactions; ROLLBACK reverts them — essential for data integrity in multi-step operations.

Tools Used

• Oracle SQL Developer
• DBMS_OUTPUT.PUT_LINE for debugging
• Google Sheets for code sharing and reference
• SQLCODE and SQLERRM for error diagnostics

Common Pitfalls

• Forgetting SET SERVEROUTPUT ON → no output visible
• Using lowercase ‘v’ instead of uppercase ‘V’ in variable declarations
• Not declaring user-defined exceptions before use
• Not closing cursors → ORA-01000: maximum open cursors exceeded
• Not using bind variables in dynamic SQL → SQL injection risk
• Omitting ELSE in CASE statements → unexpected NULL results
• Forgetting COMMIT after DDL/DML → changes not persisted

Practice Suggestions

• Practice writing PL/SQL blocks with exception handlers for NO_DATA_FOUND and TOO_MANY_ROWS.
• Convert static SQL queries into dynamic SQL using EXECUTE IMMEDIATE with bind variables.
• Create a DEPARTMENTS table and link it to EMPLOYEES with a foreign key constraint.
• Write a procedure that validates salary ranges and raises RAISE_APPLICATION_ERROR if invalid.
• Implement a nested block that modifies an outer variable and handles an exception locally.
• Log errors to a custom table using SQLCODE and SQLERRM.
• Test COMMIT and ROLLBACK with a multi-step transaction involving INSERT and UPDATE.