Oracle PL/SQL Fundamentals
Training Overview
This course builds a strong foundation in Oracle PL/SQL so participants can design, implement,
and troubleshoot robust server-side programs that power data processing and business rules.
Starting from block structure and control flow, the class advances to subprograms, packages,
cursors, collections, dynamic SQL, triggers, and performance/testing practices. Throughout,
examples echo common patterns in Aplikasi SPAN (validation, reference data management,
audit/logging, and bulk processing).
By the end, participants will be able to:
Understand core PL/SQL concepts, syntax, and programming structure.
Build and manage procedures, functions, packages, and triggers that support SPAN
processes.
Apply best practices for data processing, error handling, performance (bulk processing), and
secure coding.
Troubleshoot and profile PL/SQL code using Oracle tooling and patterns relevant to
production systems.
Audience & Prerequisites
Audience: Application developers, analysts, DBAs supporting SPAN or similar Oracle apps.
Prerequisites: Solid SQL basics (SELECT/DML/DDL), familiarity with Oracle schema objects.
Tools: Oracle Database (19c+ recommended), Oracle SQL Developer/SQLcl, sample SPAN-like
schema.
Course Chapters & Objectives
Day 1 — Core PL/SQL & Embedding SQL
Chapter 1. PL/SQL Architecture & Block Structure
Recognize the PL/SQL engine, compiler, and runtime model.
Write anonymous blocks with DECLARE–BEGIN–EXCEPTION–END.
Use lexical units, identifiers, comments, and scope rules.
Produce output and quick checks with DBMS_OUTPUT.
Chapter 2. Variables, Data Types, Records
Declare and use scalar types; apply %TYPE and %ROWTYPE for anchored typing.
Work with records and composite types for table-shaped data.
Handle conversions, NULL semantics, and assignment nuances.
Apply naming conventions that ease maintenance in large codebases.
Chapter 3. Control Structures & Basic Exceptions
Implement IF/ELSIF/ELSE, CASE, LOOP/WHILE/FOR, EXIT/CONTINUE.
Trap common runtime errors with predefined exceptions.
Use EXCEPTION blocks, RAISE, and safe patterns for WHEN OTHERS.
Understand transactional context (what commits/rollbacks do—and don’t—cover).
Chapter 4. Embedding SQL in PL/SQL
Execute SELECT INTO and DML (INSERT/UPDATE/DELETE/MERGE) inside PL/SQL.
Use SQL cursor attributes (SQL%ROWCOUNT, SQL%FOUND, etc.).
Control transactions with COMMIT/ROLLBACK/SAVEPOINT in code.
Hands-on focus: Build an input-validation block that writes audit rows and handles errors
gracefully.
Day 2 — Cursors, Subprograms, and Packages
Chapter 5. Cursors: Implicit, Explicit, & Safe Fetching
Contrast implicit vs explicit cursors; use cursor attributes.
Write CURSOR FOR loops and parameterized cursors.
Use FOR UPDATE and WHERE CURRENT OF for safe row locking.
Prevent common pitfalls (NO_DATA_FOUND, TOO_MANY_ROWS) with robust patterns.
Chapter 6. Subprograms: Procedures & Functions
Create procedures/functions; apply IN/OUT/IN OUT, defaults, and named parameters.
Overload subprograms; design for reusability and clarity.
Apply deterministic functions and purity considerations.
Grant execute, use synonyms, and expose APIs to applications (SPAN services).
Chapter 7. Packages & Modularity
Design package specs vs bodies; control visibility (public/private).
Use initialization sections and stateful vs stateless design.
Organize related procedures/functions, constants, and types in cohesive APIs.
Hands-on focus: Package a SPAN-like “reference data validation” API used by multiple
routines.
Day 3 — Exceptions, Collections, Bulk Processing, Dynamic SQL
Chapter 8. Exceptions & Application Error Patterns
Create user-defined exceptions; propagate and re-raise correctly.
Standardize errors with RAISE_APPLICATION_ERROR and error catalogs.
Capture DML errors with DBMS_ERRLOG and log contextual details.
Design an app-level error-handling template for SPAN components.
Chapter 9. Collections & Bulk Operations
Choose among associative arrays, nested tables, and varrays.
Use BULK COLLECT (with LIMIT) and FORALL for high-volume DML.
Handle partial failures with SAVE EXCEPTIONS; inspect SQL%BULK_ROWCOUNT.
Reduce SQL/PLSQL context switching and avoid row-by-row (“slow-by-slow”) patterns.
Chapter 10. Dynamic SQL & Secure Coding
Execute dynamic SQL with EXECUTE IMMEDIATE, USING binds, and RETURNING.
Compare native dynamic SQL vs DBMS_SQL.
Prevent SQL injection with bind variables and DBMS_ASSERT.
Apply definer’s vs invoker’s rights; least-privilege patterns for app code.
Hands-on focus: Bulk load and validate a transaction file; log errors; secure dynamic filters.
Day 4 — Triggers, Autonomy, File I/O & Scheduling
Chapter 11. Triggers: Row/Statement, Timing, & Use Cases
Differentiate BEFORE/AFTER, row-level vs statement-level triggers.
Use :NEW/:OLD, manage multi-row DML safely, and avoid over-triggering.
Implement business rules and audit trails; when constraints are better than triggers.
Chapter 12. Advanced & Compound Triggers
Solve mutating-table issues with compound triggers and staged state.
Order triggers, disable/enable safely, and document side effects.
Use PRAGMA AUTONOMOUS_TRANSACTION responsibly for independent logging.
Chapter 13. UTL_FILE & DBMS_SCHEDULER (Operations Basics)
Read/write CSV/flat files with UTL_FILE (path, permissions, exceptions).
Create scheduled jobs with DBMS_SCHEDULER for periodic data tasks.
Build retry/alert patterns and operational safeguards.
Hands-on focus: Audit trigger + autonomous logging + nightly scheduler cleanup.
Day 5 — Instrumentation, Testing, Performance & Capstone
Chapter 14. Instrumentation, Debugging & Profiling
Tag modules/actions with DBMS_APPLICATION_INFO for traceability.
Use SQL Developer debugger; capture call stacks and variable states.
Profile PL/SQL with DBMS_PROFILER/DBMS_HPROF; read hotspots.
Enable/request SQL Trace (10046) via DBMS_MONITOR for deep analysis.
Chapter 15. Performance & Best Practices
Prefer set-based SQL; push work to the SQL engine.
Apply bulk techniques; avoid DML in tight loops; leverage batching.
Use NOCOPY, function result cache (where available), and safe caching patterns.
Create code templates for errors, logging, and transaction boundaries.
