Отримати консультацію
Training Center MUKКурсыOracleDatabaseOracle Database 12c R2: Program with PL/SQL Ed 2

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

Код курсу
5 Днів, 40 Ак. Годин
Опис курсу
Програма курсу
Опис курсу

Course overview

This Oracle Database: Program with PL/SQL training starts with an introduction to PL/SQL and then explores the benefits of this powerful programming language. Through hands-on instruction from expert Oracle instructors, you’ll learn to develop stored procedures, functions, packages and more.


Course Objectives

  • Manage dependencies between PL/SQL subprograms
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Create stored procedures and functions
  • Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code
  • Use the Oracle supplied PL/SQL packages to generate screen output, file output and mail output
  • Write dynamic SQL for more coding flexibility
  • Create overloaded package subprograms for more flexibility
  • Create triggers to solve business challenges
  • Create and debug stored procedures and functions
  • Describe the features and syntax of PL/SQL
  • Design PL/SQL anonymous blocks that execute efficiently
  • Design PL/SQL packages to group related constructs
  • Handle runtime errors

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


  • 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
  • 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
Реєстрація на найближчий курс
Oracle Database 12c R2: Program with PL/SQL Ed 2
Код курсу:
5 Днів, 40 Ак. Годин
Отримати консультацію
Свяжитесь со мной
Отримати консультацію
Отправить заявку
Реєстрація на вебінар
Отправить заявку
Ваша заявка отримана!
Ми зв`яжимося з вами найближчим часом.