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 to create and differentiate stored procedures and functions in Oracle PL/SQL, define parameters, return values, and manage compilation errors with create or replace.

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 advanced programming techniques in PL/SQL within packages, including invoker rights versus definer rights, persistent objects, package initialization logic, object oriented principles, and advanced cursor techniques.

Expose persistent global objects by declaring global variables in the package specification to be public and accessible to all procedures, or define them in the package body for package-wide use.

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 advanced cursor variables, ref cursors, and strong versus weak definitions to enable fast data access in packages via open and fetch operations.

Learn how to replace a weak cursor definition with the tsis ref cursor, a ref cursor object managed by Oracle, eliminating global cursor variables and simplifying open and fetch operations.

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.

Understand database triggers in Oracle PL/SQL, including statement-level, row-level, and instead of triggers, as well as before/after events, cascading, view triggers, and security use cases.

Master statement-level triggers that fire once per DML event with before or after options for insert, update, or delete, including column-specific firing and handling with raise_application_error and the error stack.

Create an employee journal audit table and a row-level trigger that logs salary changes above 70000 using old and new values, then test with inserts and updates.

Explore how the instead of trigger enables updates to a view that joins multiple tables, by intercepting DML statements and executing row-level operations on the tables.

Understand prerequisites for creating triggers, including create trigger and alter table privileges, and grasp mutating table limits, row-level versus statement-level triggers, and savepoint behavior.

Learn to maintain database triggers with the call statement, delegating logic to stored procedures, passing parameters from colon new and colon old, and exploring methods to handle mutating table issues.

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.