Oracle PL/SQL Developer Bootcamp: Fundamentals to Advanced

Explore the PL/SQL environment and benefits, learn how to access the database with PL/SQL, and build modular applications using procedures, functions, packages, and triggers for improved performance and security.

Discover the benefits of PL/SQL subprograms, including modular blocks, reusable libraries, portability, and secure error handling, and learn how to invoke procedures and functions from multiple environments.

Learn the rules and structures for writing PL SQL blocks, including declarative, executable, and exception sections, declare variables with data types, and recognize anonymous blocks, triggers, and procedures and functions.

Explore PL/SQL block structure and program constructs, including anonymous and named blocks, parameters, and variables. Understand substitution and host variables in SQL*Plus and ISQL*Plus environments.

Explore scalar data types in Oracle PL/SQL, including number, character, date, boolean, and derived subtypes like integer and positive, with varchar2, long, raw, timestamp, and interval types.

Explore PL/SQL variable types such as boolean, date, blob, long, and number with precision and scale; learn declaration rules, initialization, not null and default constraints, and fetch values into variables.

Explore percentage type attributes and composite data types in PL/SQL by declaring variables, using boolean expressions, and handling lob data types, bind variables, and host references.

Explore declaring variables in pl-sql with practical examples, including data types, initialization, and anonymous blocks. Learn bind variables and output techniques using dbms_output.

Learn to write executable statements in a pl sql block, master nesting rules, and test pl sql code while applying coding conventions, identifiers, literals, and sql functions.

Learn how nested blocks define variable scope and visibility in PL/SQL, how local and global identifiers interact, and how to qualify names with block labels, plus coding guidelines.

Practice evaluating PL/SQL declarations and assignments, identifying illegal declarations, correcting assignment operators, and determining resulting data types for expressions like concatenation, date, number, and boolean.

Embed standard sql statements in pl/sql blocks to perform select and dml with transaction control. Use the into clause for variables or records and honor column name precedence.

Manipulate data in PL/SQL with insert, update, delete, and merge, using commit or rollback, sequences for keys, the on clause in merge, and avoid ambiguity with identifiers and column names.

Explore SQL cursor concepts, including implicit and explicit cursors, and how cursor attributes (SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN) help manage DML outcomes, alongside commit, rollback, and savepoints.

Explore practical examples of the merge statement for upserting data, handling on matched and not matched cases, and track results with the sql cursor attributes in Oracle pl/sql.

Explore practical PL/SQL examples, including declare and select into, printing with DBMS_OUTPUT, and performing insert, update, and delete operations on employees and departments.

Learn to control pl sql flow with if statements, case expressions, and loops, including nested loops and labels, and build conditional and loop-based logic for block execution.

Explore if then else flow, nested ifs, and else if (elseif) clauses in Oracle PL/SQL, and master case expressions with selectors, when clauses, and search case patterns.

Learn how null values impact boolean logic with and, or, not, and build complex conditions using logic tables in PL/SQL. Discover basic, for, and while loops with exit conditions.

Use while loops to repeat statements while a condition is true at the start, and for loops with lower and upper bounds, including nested loops with labels and exit.

Explore practical PL/SQL patterns: if-else and case logic, dates and substitution variables, dbms_output, and a loop inserting locations with a counter.

Execute anonymous PL/SQL blocks to declare variables and use max location_id, then insert into locations with while, for loops, showcasing country_id CA and city Montreal values.

Create and manipulate PL/SQL records using user defined record types and percent row type attributes, and describe differences between records, tables, and table of records within composite data types.

Learn to create index by tables and index by table of records. Declare table types with percent row type and use a binary integer primary key with first and last.

Discover how PL/SQL records group table rows into a single structure, enabling array-like access via indexed by tables and primary keys, with methods like exist, count, and delete.

Demonstrates declaring an employees%rowtype record, selecting into it, inserting into the employees table, and committing; and uses an index-by table to loop and print last names.

Differentiate implicit and explicit cursors in PL/SQL and declare, open, fetch, and close explicit cursors to process multiple rows, managing the active set and current row.

Fetch data from a cursor by reading each row into output variables. Advance to the next row and ensure the into clause matches the columns, handling end-of-fetch.

Close explicit cursors after processing the select statement, use explicit cursor attributes to monitor status, and exit loops safely with percent not found and percent row count.

Fetch rows into a record via a cursor to populate a temp list. Know open, not found, and row count attributes for explicit and implicit cursors.

Demonstrate declaring, opening, and fetching from explicit cursors, printing employee id and name with dbms_output, and using for loops and exit conditions for controlled data display.

Learn to write explicit cursors with parameters and the for update clause, use where current of, and open and reopen cursors with different parameter sets.

Use the where current of clause with an explicit for update cursor to update or delete the currently fetched row, and learn about subqueries and cursor parameters.

Shows how to use advanced explicit cursors in Oracle PL/SQL, including for update and where current of clauses to conditionally raise salaries, with cursor loops and end block.

Learn to define and trap PL/SQL exceptions with predefined, non predefined, and user defined handlers, manage propagation in nested blocks, and use others to catch unanticipated errors.

Learn to trap non predefined Oracle server errors in PL/SQL by declaring exceptions, mapping them with pragma exception_init, and handling them via when others and user defined exceptions.

Define and raise user defined exceptions in PL/SQL, propagate to calling environments, and handle them with dedicated exception blocks, using pragma exception_init and res_application_error.

Explore practical PL/SQL exception handling with non predefined and user defined errors, using pragma exception_init, exception blocks, and dbms_output to manage delete and update operations with commit.

Explore anonymous and named PL/SQL blocks, subprograms, and procedures, with hands-on guidance to create, invoke, and remove procedures, and to manage parameters and exceptions.

Learn how to define PL/SQL subprograms, including procedures and functions with headers, parameters, and return clauses, and implement executable and exception sections for modular, secure, and efficient database programming.

Explore how to invoke stored procedures and functions from diverse Oracle tools, and learn to create, compile, and execute procedures with parameters in PL/SQL for reusable database actions.

Explain the distinction between formal parameters declared in a subprogram and actual parameters used in a call, and describe in, out, and in out modes with examples.

Learn how to declare and use out and inout parameters in Oracle PL/SQL with examples, including a query_emp procedure returning name, salary, and commission, and a format_phone procedure.

Learn methods for passing parameters to PL/SQL subprograms, including positional and named association list values, with defaults allowed for in parameters. Declare local subprograms and organize code with packages.

Invoke procedures from anonymous PL/SQL blocks and from other procedures, observe commit-driven transactions, handle and illustrate both handled and unhandled exceptions, and learn to drop procedures.

Learn how a procedure acts as a subprogram, uses parameters to return values, can be saved as a stored procedure in the database, and how exceptions affect transactions.

The lecture demonstrates creating and compiling Oracle PL/SQL procedures, including res_salary and query_EMP with input and output parameters, and formatting in format_phone with in out parameters.

Create or replace procedure add_dept with default parameter values and insert into departments using a sequence, then call via anonymous blocks to verify outputs.

Create and invoke Oracle PL/SQL procedures with practical examples, including log_execution, cursor-driven processing, and single-procedure and multi-procedure calls, plus handled and unhandled exceptions.

Learn to create, invoke, and manage stored functions in Oracle PL/SQL, with attention to parameters, return clause, header, declarative and executable parts, and function versus procedure.

Create and compile stored functions using iSQL Plus, load scripts, and invoke them from PL/SQL expressions. Handle compilation errors, return a single value, and avoid side effects.

Explore invoking and removing user defined functions in SQL expressions, including creating or replacing functions, calling them from queries, and realizing benefits like performance, maintainability, and security.

Explore how to create, drop, and invoke functions in PL/SQL as part of expressions, understand the difference between functions and procedures, and gain practical demonstrations in session ten of PL/SQL.

Explore creating and using user-defined functions in Oracle PL/SQL, including get_cell and tax functions, with practical examples of select, insert, and update.

Explore system and object privileges, using data dictionary views to manage stored objects; implement invoker rights with auth_id current_user and compare definer rights, debugging subprograms with dbms_output.

Access and display PL/SQL source and errors with user_source, all_source, and dba_source views, regenerate from user_source, and inspect or debug with show errors and dbms_output.

Demonstrates managing subprograms by creating a package including a query_employee procedure with in and out parameters, listing procedures, describing objects, and diagnosing compilation errors with user_errors and log_execution.

Discover how to create and use Oracle PL/SQL packages, including specification and body, public versus private constructs, and how packaging groups related variables, cursors, and subprograms for efficient reuse.

Learn to create or replace package body and bodiless packages in Oracle PL/SQL, validate and reset subprograms, and invoke package constructs from inside or outside the package, and remove packages.

Practice creating and compiling Oracle PL/SQL packages, including package specs and bodies, with procedures and functions, validation logic, error handling, borderless constants, and package management.

Learn advanced PL/SQL concepts, including overloading and forward referencing in packaged subprograms, one-time initialization, and the persistence of variables, constants, exceptions, and cursors, plus how packaging affects SQL calls.

Explore overloading in PL/SQL with built-in functions like two_underscore_car that accept date or number and convert to character. Learn forward declarations, package visibility, and one-time-only procedures.

Explore user defined packages in Oracle PL/SQL, including purity restrictions for functions called from SQL, using pragma restrict_references, and managing persistent package variables as demonstrated in taxes_pack.

Explore the persistent state of a package cursor in Oracle PL/SQL, including declaring a public cursor, opening and fetching rows, and persisting state across transactions within a session.

Explore overloading packages in Oracle PL/SQL with practical examples of add_dept procedures, default parameters, and package body implementations that insert into departments using sequence values.

Create and replace packages and package bodies, including a tax function returning salary times 0.08, and a cursor-based package with procedures that print ids using dbms_output.

Learn to use Oracle server supplied packages dbms_sql, dbms_ddl, and dbms_job to write dynamic sql, execute immediate, and schedule jobs with proper parsing, binding, and execution.

Explore dynamic SQL in Oracle PL/SQL using dbms_sql and execute immediate, including opening cursors, executing statements, and returning row counts from delete operations.

Learn to manage Oracle jobs with the DBMS_JOB package: submit, change, and remove jobs, set next dates and intervals, handle broken jobs, and run or view job details.

Use the dbms_output package to output messages from PL/SQL blocks with put, new_line, and get_line/get_lines; use the utl_file package for text IO and file operations.

Explore how UTL_HTTP and UTL_TCP enable PL/SQL applications to make HTTP requests and establish TCP connections from the database, while using UTL_FILE for file I/O and handling errors.

Explore Oracle supplied packages through practical demos of DBMS_SQL, including delete all rows and delete using execute immediate, plus a sample of utl_file usage.

Learn to use Oracle supplied packages to generate a salary report with UTL_FILE, including SELECT statements, file write operations, and exception handling.

Explore how Oracle lob data types, which include blob, clob, nclob, and bfile, store large unstructured data and use internal and external lobs with BFILE locators.

Explore lobs stored in the Oracle server, including blob, clob, and nclob, and manage them via locator, select for update, and dbms_lob, then review b files as external large objects.

Create and manage Oracle directory objects to point to operating system file paths. Grant read privileges and use the BFILE name function with PL/SQL and OCI for external file access.

Load employee images from bmp files into the employees table using bfile. Migrate long columns to lob with alter table modify, and explore clob/blob conversion in long to lob migration.

Explore how the dbms_lob package enables reading and writing large objects in Oracle pl sql, detailing mutator and observer routines such as append, copy, erase, and trim.

Learn how to remove lobs in PL/SQL, update rows to null or empty for lob columns, erase external files, and use dbms_lob to read, write, and create temporary lobs.

Alter table employees add resume (clob) and picture (blob), then insert, update, and select lob data using dbms_lob functions. Create and manipulate temporary lob values with is_temp_lob and dbms_lob functions.

Study types of triggers, including DML and instead of triggers, and master their firing sequence on tables and views. Learn guidelines and examples such as salary checks and audit actions.

Explore DML trigger components and firing sequence in Oracle PL/SQL, including before, after, and instead of triggers, and the difference between statement and row triggers.

Describe how to create or replace triggers for DML in Oracle PL/SQL, including before and after triggers, per-statement and per-row variants, conditional predicates, and error handling.

Explore old and new qualifiers in Oracle triggers, log data changes to an audit table, and use when clauses and instead of triggers to manage inserts, updates, and deletes.

Learn how to create an instead of trigger on a view to manage insert, update, and delete operations by redirecting changes to base tables, with examples and key syntax.

Learn drop trigger syntax and how to test triggers, explore the trigger execution model with before/after and row/statement triggers, and understand constraint checks and cascading effects.

Explore after row and after statement triggers in Oracle PL/SQL, using audit_emp and audit_emp_tab to track DML on employees via package procedures.

Create or replace trigger on the employees table named secure_emp to enforce business hours. Raise application errors for disallowed inserts or updates outside these hours.

Learn to create DML row triggers in Oracle PL/SQL, including a before trigger that restricts salaries to 15000 for specific job ids and an after trigger that audits changes.

Master creating and testing Oracle PL/SQL triggers, including before insert or update on employees, handling new and old values, and updating commission_pct with a 0.05 increment.

Create tables new_emp and new_dept and a view employee_details joining employees and departments, then implement an instead-of trigger on the view to route inserts and sync updates and deletes.

Create and manage an instead of trigger in Oracle PL/SQL that handles insert into a view by updating tables, includes delete and update branches, and demonstrates enabling and disabling triggers.

Explore creating advanced database triggers in Oracle PL/SQL, including ddl and system-event triggers, with logon/logoff audits and trigger body calls to procedures.

Explore implementing Oracle triggers to enforce data integrity and security, address mutating table constraints, and enable auditing, referential integrity, and derived data through practical examples.

Explore enforcing data integrity with declarative constraints and triggers, audit trails and referential integrity, and replicate tables using snapshots or triggers for robust Oracle PL/SQL database programming.

Learn how Oracle PL/SQL uses triggers to log events and send messages via dbms_pipe, and how to view and list trigger code.

Explore practical demonstrations of Oracle PL/SQL triggers, including log on/off triggers and mutating table checks. Learn salary validation with raise_application_error and cascade updates to preserve referential integrity.

Track procedural dependencies and predict how changes to a referenced object affect stored procedures and functions, while Oracle automatically recompiles invalidated objects and manages local dependencies.

Learn to display direct and indirect dependencies in Oracle PL/SQL using dp_tree and edep_tree views, populate dependency metadata, and predict recompilation effects for local and remote dependencies.

Examine how remote dependencies mode and time span mode control PL/SQL unit dependencies, causing invalidation and recompile behavior, and compare automatic local dependency handling with manual recompilation strategies.

Apply signature mode to recompile remote procedures without disturbing local ones in distributed databases. Manage dependencies and package bodies to ensure successful recompilation and valid objects.