Oracle 11g PL/SQL Fundamentals II

Prepare a Windows workstation for Oracle XE by downloading Oracle X-C package, installing the sequel developer, and configuring scripts, with attention to system requirements, password, and ports 1521, 2030, 1880.

Install SQL Developer by copying files and launching the executable, avoiding a full Windows install. Use database scripts to create and populate tables, emphasizing commit and rollback.

Set up your Oracle 11g PL/SQL Fundamentals II workstation, install Oracle and SQL Developer, connect to a company database, run scripts, create and populate tables, and inspect output for errors.

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.

Master executing procedures and functions in Oracle 11g PL/SQL, using remote calls or SQL Plus and SQL Developer. Learn to manage execution rights, exceptions, and parameter notation (positional, named, mixed).

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.

Explore managing dependencies and recompiling or dropping procedures and functions. Learn how Oracle tracks programs in the data dictionary and handles invalid states, with automated and manual recompilation.

Learn to manage dependencies in Oracle PL/SQL by tracking dependent objects, understanding time stamp versus signature invalidation, and addressing local versus distributed databases.

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.

Learn how to create and maintain packages in Oracle PL/SQL, understand the header specification and body implementation, and how public and private program units boost performance, memory management, and maintainability.

Create an Oracle 11g PL/SQL II package by defining a specification and a body, exposing procedures like hire employee, transfer employee, and raise salary, with private and public components.

Explore how to retrieve and inspect Oracle package definitions in SQL Developer, using describe and user objects view to view public package and package body details, parameters, and validity.

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.

Maintain Oracle packages by tracking dependencies, selectively compiling specification or body, and using explicit instantiation to boost performance while preserving package validity.

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.

Learn how to manage multiple triggers on a table in Oracle 11g, understand execution order from before to after triggers, and use the follows clause to enforce firing sequence.

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.