Course Details
In this course, you will learn about the Oracle 12c PL/SQL programming language. You will be able to control data sharing and locking, develop an understanding of multi-user and concurrent transactions, and develop triggers, procedures, functions, and packages.
Course Prerequisites
Oracle 12c Foundations: SQL and SQL*PLUS (OR103) or equivalent experience
Course Agenda
1. Introduction to PL/SQL
- History of PL/SQL
- Features and Benefits of PL/SQL
- Relationship of PL/SQL to SQL
- PL/SQL Development Tools
- Native Compilation
2. PL/SQL Basics
- PL/SQL Anonymous Block Structure, Lexical Units, Variable Declarations, Types, and Records
- SQL*Plus Development Environment
- Displaying Messages with DBMS_OUTPUT
- Object Naming Rules, and PL/SQL Style Guide and Coding Conventions
3. Working with Database Data
- SELECTing Single Rows
- Declaring Variable Datatypes Dynamically
- Modifying Database Data (DML)
- Transaction Control Statements
4. Selecting Multiple Rows Using Cursors
- Declaring Explicit Cursors
- Implicit Cursor Attributes
- Using the Cursor FOR LOOP
5. Exception Handling
- Writing an Exception Handler Section
- Handling Predefined Exceptions, Controlling Exception Processing – Exception Propagation
- RAISE_APPLICATON_ERROR Use
- Preventing Unhandled Exceptions
- Exception Propagation
- Using PRAGMA EXCEPTION_INIT
6. Advanced Cursors
- Cursor Parameters
- Taking Advantage of a Weak Cursor Variable
- OPEN FOR, FETCH, and CLOSE
- Using the FOR UPDATE Clause
- Using PL/SQL Collections and Nested Collections
7. Introduction to Procedures and Functions
- Creating Stored PL/SQL Objects, Procedures, Functions
8. Creating Packages
- Creating Package Specifications and Bodies
- One Time Only Procedures
- Persistent State
9. Creating DML Triggers
- Triggering Events and Trigger Behavior
- Correlation Identifiers and Multi-statement Triggers
- Trigger Firing Behavior and Enabling/Disabling Triggers
10. Advanced Packages
- Initializing Variables
- Module Overloading
- Recursion
- Purity Levels
- Advanced Triggers
- Trigger Limitations, Mutating, and Constraining Tables
- Using CALL and Client Triggers
- DDL Triggers
- Using SERVERERROR Event
- Schema vs. Database Triggers
- Using Alternative Events and Levels
- INSTEAD OF Triggers on Views
12. PL/SQL Composite Datatypes and Collections
- PL/SQL Records, PL/SQL Associative Arrays, and Arrays of Records
- Using PL/SQL Record Variables
- PL/SQL Collections
13. Bulk-Bind Data Loading Using PL/SQL
- Defining Bulk Binds
- Error Handling with Bulk Binds
14. Using Oracle Supplied Packages
- DBMS_OUTPUT Package
- UTL_FILE package (file i/o)
- DBMS_ALERT/PIPE Packages
- DBMS_JOB Package
- DBMS_SCHEDULER Package
- DBMS_STATS Package
- DBMS_UTILITY Package
- UTL_SMTP/MAIL Packages
- DBMS_SQL Package
15. Writing Native Dynamic SQL
- EXECUTE IMMEDIATE
16. PL/SQL Wrapper
- PL/SQL Wrapper (source code encryption)
17. Understanding Dependencies
- Viewing Dependencies
- Effect of Breaking Dependency Chain
18. Large Object Management in PL/SQL
- Differences between LONG/LONG RAW and LOBs
- Creating and Using BFILEs, and Tables with LOBs
- LOBs and PL/SQL
- DBMS_LOB Capabilities
- Temporary LOBs
19. Objects
- Basic Objects
- Object Inheritance