Oracle Program with PL-SQL - 5 days Online Classroom

Course timeline

1. PL/SQL Fundamentals, Program Logic & Cursors

   Day 1

   • Understand the core concepts of PL/SQL, including syntax, variables, constants, and Oracle data types.
   • Write PL/SQL blocks that execute SQL statements and generate output using SQL Developer.
   • Control program execution with IF statements, CASE expressions, loops, and branching techniques.
   • Develop structured logic using WHILE, FOR, nested, and labelled loops.
   • Learn the differences between implicit and explicit cursors for retrieving database records.
   • Create, manage, and use cursors to fetch, update, and process data efficiently.

2. Exception Handling & PL/SQL Collections

   Day 2

   • Handle runtime errors using Oracle's exception-handling framework.
   • Work with system-defined, user-defined, and application-specific exceptions.
   • Understand exception propagation, nested blocks, and variable scope.
   • Create and use PL/SQL record types to organise related data.
   • Build and manipulate associative arrays (index-by tables) for in-memory data processing.
   • Apply collection methods to efficiently manage and process datasets.

3. Triggers, Procedures & Functions

   Day 3

   • Create and manage DML, DDL, system event, and compound triggers to automate database operations.
   • Implement trigger logic to enforce business rules and maintain data integrity.
   • Develop reusable stored procedures with parameters, local subprograms, and autonomous transactions.
   • Build PL/SQL functions that can be called from SQL and application code.
   • Learn about invoker's rights, definer's rights, and function performance features such as result caching.
   • Manage database objects, privileges, and metadata for procedures, functions, and triggers.

4. Packages, REF Cursors & Dependency Management

   Day 4

   • Design PL/SQL packages with public and private components for modular application development.
   • Use records, associative arrays, bulk processing, and the NOCOPY hint within packages.
   • Work with REF cursors to return and process dynamic result sets.
   • Pass cursor variables between procedures and functions for flexible data access.
   • Understand object dependencies, recompilation, and managing invalid database objects.
   • Apply best practices for maintaining reliable and scalable PL/SQL applications.

5. Advanced PL/SQL Features & Best Practices

   Day 5

   • Improve application performance using native compilation and compiler warnings.
   • Use Oracle-supplied packages such as DBMS_SQL, DBMS_METADATA, DBMS_SCHEDULER, and UTL_MAIL.
   • Build dynamic SQL solutions for flexible database programming.
   • Implement coding standards for variables, constants, exceptions, and reusable program structures.
   • Optimise PL/SQL applications using features such as PARALLEL ENABLE, NOCOPY, and autonomous transactions.
   • Apply professional PL/SQL development techniques to build secure, efficient, and maintainable Oracle applications.