Oracle Database 12c R2: Program with PL/SQL Ed 2

Suggested Prerequisite

• Oracle Database: Introduction to SQL
• Previous programming experience

Required Prerequisite

• Oracle Database: SQL Workshop II Ed 2
• Oracle Database: SQL Workshop I Ed 2

Audience

• Application Developers
• Database Administrators
• System Analysts
• Forms Developer
• Developer
• Technical Consultant
• Portal Developer
• PL/SQL Developer

Course Topics

1. Introduction

• PL/SQL development environments available in this course
• Introduction to SQL Developer
• Course Objectives
• Describe the Human Resources (HR) Schema
• Course Agenda

2. Working with Oracle Cloud Exadata Express Cloud Service

• Introduction to Oracle Database Exadata Express Cloud Service
• Connecting to Exadata Express using Database Clients
• Accessing Cloud Database using SQL Workshop

3. Introduction to PL/SQL

• Identify the benefits of PL/SQL Subprograms
• Create a Simple Anonymous Block
• Overview of PL/SQL
• How to generate output from a PL/SQL Block?
• Overview of the types of PL/SQL blocks

4. Declare PL/SQL Variables

• Sequences in PL/SQL Expressions
• The %TYPE Attribute
• Use variables to store data
• List the different Types of Identifiers in a PL/SQL subprogram
• What are Bind Variables?
• Identify Scalar Data Types
• Usage of the Declarative Section to Define Identifiers

5. Write Anonymous PL/SQL Blocks

• Describe Nested Blocks
• Describe Basic PL/SQL Block Syntax Guidelines
• How to convert Data Types?
• Learn to Comment the Code
• Identify the Operators in PL/SQL
• Deployment of SQL Functions in PL/SQL

6. SQL Statements in a PL/SQL block

• Retrieve Data in PL/SQL
• Data Manipulation in the Server using PL/SQL
• Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
• Understand the SQL Cursor concept
• Invoke SELECT Statements in PL/SQL
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions
• SQL Cursor concept

7. Control Structures

• Conditional processing using IF Statements
• Conditional processing using CASE Statements
• Use the Continue Statement
• Describe While Loop Statement
• Describe simple Loop Statement
• Describe For Loop Statement

8. Composite Data Types

• The %ROWTYPE Attribute
• Use PL/SQL Records
• Insert and Update with PL/SQL Records
• Examine INDEX BY Table Methods
• INDEX BY Tables
• Use INDEX BY Table of Records

9. Explicit Cursors

• Describe the FOR UPDATE Clause and WHERE CURRENT Clause
• Declare the Cursor
• What are Explicit Cursors?
• Fetch data from the Cursor
• Cursor FOR loop
• Close the Cursor
• The %NOTFOUND and %ROWCOUNT Attributes
• Open the Cursor

10. Exception Handling

• Trap User-Defined Exceptions
• Trap Non-Predefined Oracle Server Errors
• Understand Exceptions
• RAISE_APPLICATION_ERROR Procedure
• Propagate Exceptions
• Trap Predefined Oracle Server Errors
• Handle Exceptions with PL/SQL

11. Stored Procedures

• List the benefits of using PL/SQL Subprograms
• List the differences between Anonymous Blocks and Subprograms
• Create a Modularized and Layered Subprogram Design
• Implement Procedures Parameters and Parameters Modes
• Create, Call, and Remove Stored Procedures
• Modularize Development With PL/SQL Blocks
• Understand the PL/SQL Execution Environment
• View Procedure Information

12. Stored Functions

• Identify the steps to create a stored function
• Control side effects when calling Functions
• Create, Call, and Remove a Stored Function
• View Functions Information
• Restrictions when calling Functions
• Identify the advantages of using Stored Functions
• Invoke User-Defined Functions in SQL Statements

13. Debugging Subprograms

• Debugging through SQL Developer
• How to debug Functions and Procedures?

14. Packages

• Describe Packages
• What are the components of a Package?
• Develop a Package
• Invoke the Package Constructs
• View the PL/SQL Source Code using the Data Dictionary
• How to enable visibility of a Packages Components?
• Listing the advantages of Packages
• Create the Package Specification and Body using the SQL CREATE Statement and SQL Developer

15. Deploying Packages

• Persistent State of a Package Cursor
• Overloading Subprograms in PL/SQL
• Use Forward Declarations to solve Illegal Procedure Reference
• Control side effects of PL/SQL Subprograms
• Use the STANDARD Package
• Persistent State of Packages
• Invoke PL/SQL Tables of Records in Packages
• Implement Package Functions in SQL and Restrictions

16. Implement Oracle-Supplied Packages in Application Development

• Examples of some of the Oracle-Supplied Packages
• Invoke the UTL_MAIL Package
• Use the UTL_FILE Package to Interact with Operating System Files
• How does the DBMS_OUTPUT Package work?
• What are Oracle-Supplied Packages?
• Write UTL_MAIL Subprograms

17. Dynamic SQL

• The Execution Flow of SQL
• Declare Cursor Variables
• Configure Native Dynamic SQL to Compile PL/SQL Code
• Dynamic SQL Functional Completeness
• What is Dynamic SQL?
• Dynamically Executing a PL/SQL Block
• How to invoke DBMS_SQL Package?
• Implement DBMS_SQL with a Parameterized DML Statement

18. Design Considerations for PL/SQL Code

• Understand Local Subprograms
• Implement the NOCOPY Compiler Hint
• The Cross-Session PL/SQL Function Result Cache
• Usage of Bulk Binding to Improve Performance
• Standardize Constants and Exceptions
• The DETERMINISTIC Clause with Functions
• Write Autonomous Transactions
• Invoke the PARALLEL_ENABLE Hint

19. Triggers

• Identify the Trigger Event Types and Body
• How to Manage, Test and Remove Triggers?
• Identify the Trigger Event Types, Body, and Firing (Timing)
• Differences between Statement Level Triggers and Row Level Triggers
• Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
• Describe Triggers
• Business Application Scenarios for Implementing Triggers
• Create Instead of and Disabled Triggers

20. Creating Compound, DDL, and Event Database Triggers

• Implement a Compound Trigger to Resolve the Mutating Table Error
• Identify the Timing-Point Sections of a Table Compound Trigger
• Comparison of Database Triggers to Stored Procedures
• What are Compound Triggers?
• Create Database-Event and System-Events Triggers
• Understand the Compound Trigger Structure for Tables and Views
• System Privileges Required to Manage Triggers
• Create Triggers on DDL Statements

21. PL/SQL Compiler

• Overview of PL/SQL Compile Time Warnings for Subprograms
• List the PL/SQL Compile Time Warning Messages Categories
• Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
• View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
• List the benefits of Compiler Warnings
• What is the PL/SQL Compiler?
• Describe the Initialization Parameters for PL/SQL Compilation
• List the new PL/SQL Compile Time Warnings

22. Manage Dependencies

• Query Direct Object Dependencies using the USER_DEPENDENCIES View
• Overview of Schema Object Dependencies
• Invalidation of Dependent Objects
• Recompile a PL/SQL Program Unit
• Understand Remote Dependencies
• Query an Objects Status
• Fine-Grained Dependency Management in Oracle Database 12c
• Display the Direct and Indirect Dependencies