Cloud Desktop Teaching Platforms

This final day of training focused on advanced PL/SQL programming concepts, emphasizing dependency management, debugging techniques, parameter passing, recompilation of subprograms, and comprehensive trigger implementation (row-level, statement-level, conditional). The session culminated in hands-on coding exercises covering dynamic SQL for DDL/DML operations, with emphasis on error handling, data type validation, and secure query construction. The trainer guided participants through real-time debugging, troubleshooting, and best practices for maintaining robust, maintainable PL/SQL applications.

Topic (Timeline)

1. Dependency Management and Subprogram Hierarchy [00:00:36.880 - 00:02:13.100]

Discussed the impact of dropping dependent functions: removing a subprogram (e.g., get_width) invalidates dependent functions (e.g., calculate_area).
Emphasized the importance of understanding subprogram dependencies for managing complex database applications.
Demonstrated querying dependencies using system views to identify all subprograms referenced by a main function.

2. Debugging with Custom Debug Package [00:02:16.380 - 00:05:06.000]

Introduced a custom debug package for logging execution flow, parameter values, and intermediate results in PL/SQL.
Covered advanced debugging features: sys_timestamp for precise logging, dynamic log levels (debug, info, error), and logging to a persistent custom table.
Demonstrated a sample debug workflow: logging input validation, calculation steps, and error conditions (e.g., division by zero).
Highlighted benefits: enhanced troubleshooting, clear separation of log levels, and reusable, consistent error reporting across subprograms.

3. Best Practices for Debugging and Parameter Passing [00:05:08.560 - 00:07:13.660]

Best practices: validate all inputs, handle edge cases, integrate debug logs with business logic, and use a package-level toggle to enable/disable debugging at runtime.
Explained positional vs. named parameter notation: positional for short lists, named for clarity with long lists or default values.
Described mixed notation: named parameters must follow all positional parameters.
Benefits of named notation: reduced ambiguity, explicit parameter intent, and selective override of defaults without re-specifying all parameters.

4. Recompilation of Invalid Subprograms [00:07:16.520 - 00:09:29.440]

Explained why recompilation is necessary: dependency changes (e.g., table/view modifications) invalidate procedures/functions.
Demonstrated querying user_objects to identify invalid objects using the status column.
Showed dynamic recompilation using EXECUTE IMMEDIATE 'ALTER PROCEDURE/FUNCTION ... COMPILE'.
Emphasized best practices: test after recompilation, track schema changes, and recompile only specific objects to ensure application integrity.

5. Triggers: Fundamentals and Row-Level Implementation [00:09:30.140 - 00:16:02.960]

Defined triggers as PL/SQL blocks that auto-execute in response to DML events (INSERT, UPDATE, DELETE) on tables/views.
Covered trigger components: name, timing (BEFORE/AFTER/INSTEAD OF), event type, and scope (ROW vs. STATEMENT).
Conducted hands-on coding: implemented a row-level trigger to log employee salary changes into an audit table.
Debugged trigger failures: identified missing old_salary column and clarified that AFTER UPDATE triggers do not fire on INSERT.
Emphasized best practices: validate meaningful changes, monitor audit table growth, and include exception handling.

6. Statement-Level and Conditional Triggers [00:16:03.900 - 00:21:14.620]

Explained statement-level triggers: execute once per DML statement, regardless of affected rows; ideal for logging or enforcing rules at the statement level.
Introduced the WHEN clause to conditionally execute triggers based on column values (e.g., log only if :new.salary > 10000).
Demonstrated use of :new and :old pseudo-records in WHEN conditions for row-level filtering.
Conducted coding exercises to implement conditional logging and validate trigger behavior with test cases.

7. Selective Trigger Logic with IF Statements [00:21:14.800 - 00:24:26.480]

Demonstrated using IF statements within trigger bodies for complex business rules (e.g., prevent deletion of HR department).
Implemented a BEFORE DELETE row-level trigger on departments table with IF :new.department_name = 'HR' THEN RAISE_APPLICATION_ERROR.
Troubleshot trigger failures: corrected case sensitivity, column name mismatches (department_name vs. dept_name), and recompilation issues.
Confirmed trigger behavior: HR department deletion was blocked with custom error message, while other departments were deletable.

8. Dynamic SQL: DDL/DML Execution and Best Practices [00:25:31.940 - 01:41:16.660]

Introduced dynamic SQL using EXECUTE IMMEDIATE to run DDL (CREATE, DROP) and DML (UPDATE, INSERT) statements at runtime.
Demonstrated creating a temporary table dynamically with variable column definitions.
Emphasized best practices: use exception blocks, validate SQL string before execution, and bind parameters to prevent SQL injection.
Highlighted use cases: dynamic object names, variable table/column structures, and runtime query construction.

9. Dynamic Query Construction and Data Type Validation [01:41:19.260 - 02:00:17.600]

Conducted hands-on exercise: dynamically updating employee salaries by department using EXECUTE IMMEDIATE with bind variables.
Debugged runtime error: data type mismatch between VARCHAR2(50) and NUMBER in variable declarations.
Corrected issue by properly defining v_name as VARCHAR2(50) and v_dept_id as NUMBER.
Concluded with recommendation to validate data types and structure before dynamic execution to avoid runtime failures.

Appendix

Key Principles

Dependency Awareness: Always check dependencies before dropping or altering subprograms.
Debugging Strategy: Use structured logging with levels (debug/info/error) and persistent storage for long-term analysis.
Parameter Clarity: Prefer named notation for functions with >3 parameters or default values to improve readability and reduce errors.
Recompilation Discipline: Recompile only when dependencies change; always verify status post-recompilation.
Trigger Scope: Use row-level triggers for per-row logic (e.g., audit, validation); use statement-level for bulk operations (e.g., logging).
Dynamic SQL Safety: Never concatenate user input into SQL strings; always use bind variables and validate structure.

Tools Used

PL/SQL Developer / SQL*Plus (implied)
Custom debug package with logging table
user_objects view for dependency/status checks
DBMS_OUTPUT.PUT_LINE for debugging output
EXECUTE IMMEDIATE for dynamic SQL

Common Pitfalls

Forgetting to recompile invalid subprograms after schema changes.
Misunderstanding AFTER UPDATE vs. AFTER INSERT trigger timing.
Using :new/:old in statement-level triggers (invalid; only available in row-level).
Data type mismatches in dynamic SQL variable declarations (e.g., NUMBER vs. VARCHAR2).
Not using bind variables in dynamic SQL, exposing to SQL injection.

Practice Suggestions

Create a dependency map for key application packages.
Implement a reusable debug package with configurable log levels.
Write triggers for audit logging on all critical tables.
Practice dynamic SQL with variable table names and column filters.
Test edge cases: null inputs, zero values, duplicate keys, and constraint violations.

ORACLE PL/SQL Fundamentals - rey-njhi-20241212-090008

Visit NobleProg websites for related course

Summary

Overview