Oracle 11g PL/SQL Fundamentals II

Explore how Oracle PL/SQL database program units split work between application and database servers to boost performance, covering anonymous and stored units, packages, procedures, functions, and triggers.

Learn how the parameter specification passes values into programs by distinguishing actual and formal parameters, including mode, unconstrained data types, and default values; apply to procedures and functions with examples.

Learn how to call user defined functions from SQL in Oracle, enabling custom business rules and tests, usable where built-in functions are used, with upper, round, and salary valid result.

Learn how Oracle handles compilation errors: invalid program units are stored in the database and surfaced through user_errors; use show errors and the compiler log to diagnose and fix.

Track dependencies in Oracle 11g PL/SQL using the user dependencies view or the dependency tracking utility. Understand multi-level chains and setup steps to manage changes propagating to dependent objects.

Explore initialization logic in Oracle 11g PL/SQL by using a bottom-of-body anonymous begin block to initialize a global salary cap and expose it with a public function.

Explore how cursor expression enables querying across one-to-many relationships by embedding a weak, explicit cursor as a column, creating a nested ref cursor for hierarchical data.

Learn how to use system supplied packages like dbms_output to post messages and control the display buffer, and use getline and getlines to retrieve lines for logging and debugging.

Learn to use the UTL_FILE package to read and write external files by creating directory objects, opening files, writing lines, and managing file handles in Oracle 11g.

Explore handling mutating table issues in Oracle by using a row-level trigger with a global state in a package and a statement-level trigger to apply updates.

Create a compound trigger in Oracle 11g PL/SQL to replace mutating-table workarounds with a single cohesive trigger, sharing a declaration section and handling after row and after statement logic.