Oracle Database: Develop PL/SQL Program Units

What you will learn:

This course is designed for developers with basic PL/SQL and SQL language skills. Developing,

executing, and managing PL\SQL stored program units are dealt with in detail. Participants can

enhance their skills by gaining in-depth knowledge of PL\SQL stored program units such as

procedures, functions, packages, and database triggers. Participants also learn to utilize some of

the Oracle-supplied packages.

Additionally the course also talks about the usage of Dynamic SQL along with enforcing the

design considerations when coding using PL/SQL. Influencing the PL/SQL compiler and

managing dependencies also provide the participants with better understanding of the language.

Additionally, participants use Oracle SQL Developer as the main tool and SQL*Plus is available

as an optional tool.

This is appropriate for a 10g and 11g audience. There are minor changes between 10g and 11g

features in SQL.

Learn to:

• Design PL/SQL packages

• Create overloaded package subprograms for more flexibility.

• Utilize Oracle‐Supplied Packages in Application Development

• Create triggers to solve business challenges.

• Execute SQL statements dynamically.

• Create stored procedures and functions.

Audience:

• Forms Developer

• System Analysts

• Support Engineer

• Application Developers

• PL/SQL Developer

• Database Administrators

Prerequisites:

Required Prerequisites:

• Oracle Database: Introduction to SQL (combination of Oracle Database: SQL Fundamentals I and

Oracle Database: SQL Fundamentals II)

• Basic Knowledge of PL/SQL

• Oracle Database: SQL Fundamentals II

• Oracle Database: SQL Fundamentals I

Suggested Prerequisites:

• Previous programming experience

Course Objectives:

• Use the compiler warnings infrastructure.

• Create triggers to solve business challenges.

• Manage dependencies between PL/SQL subprograms.

• Use conditional compilation to customize the functionality in a PL/SQL application without

removing any source code.

• Create stored procedures and functions.

• Design PL/SQL packages to group and contain related constructs.

• Create overloaded package subprograms for more flexibility.

• Use the Oracle supplied PL/SQL packages to generate screen output, file output, and mail

output.

• Write dynamic SQL for more coding flexibility.

• Use PL/SQL packages to group and contain related constructs.

• Design PL/SQL code for pre‐defined data types, local subprograms, additional programs and

standardized constants and exceptions.

Course Topics:

Introduction

• Course Objectives and Agenda

• Describe the Human Resources (HR) Schema

• Identify the Appendices Used in this Course

• List the PL/SQL Development Environments Available in this Course

• Use SQL Developer

• Review the Online Oracle 11g SQL and PL/SQL Documentation and the Additional Available

Resources

Create Stored Procedures

• Create a Modularized and Layered Subprogram Design

• Modularize Development With PL/SQL Blocks

• Describe the PL/SQL Execution Environment

• Identity the benefits of Using PL/SQL Subprograms

• List the differences Between Anonymous Blocks and Subprograms

• Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer

• Implement Procedures Parameters and Parameters Modes

• View Procedures Information Using the Data Dictionary Views and SQL Developer

Create Stored Functions

• Create, Call, and Remove a Stored Function Using the CREATE Command and SQL Developer

• Identity the advantages of Using Stored Functions in SQL Statements

• List the steps to create a stored function

• Implement User‐Defined Functions in SQL Statements

• Identity the restrictions when calling Functions from SQL statements

• Control Side Effects when calling Functions from SQL Expressions

• View Functions Information

Create Packages

• Identity the advantages of Packages

• Describe Packages

• List the components of a Package

• Develop a Package

• How to enable visibility of a Package’s components?

• Create the Package Specification and Body Using the SQL CREATE Statement and SQL Developer

• Invoke Package Constructs

• View PL/SQL Source Code Using the Data Dictionary

Packages

• Overloading Subprograms in PL/SQL

• Use the STANDARD Package

• Use Forward Declarations to Solve Illegal Procedure Reference

• Implement Package Functions in SQL and Restrictions

• Persistent State of Packages

• Persistent State of a Package Cursor

• Control Side Effects of PL/SQL Subprograms

• Invoke PL/SQL Tables of Records in Packages

Implement OracleSupplied

Packages in Application Development

• What are Oracle‐Supplied Packages?

• Examples of Some of the Oracle‐Supplied Packages

• How Does the DBMS_OUTPUT Package Work?

• Use the UTL_FILE Package to Interact With Operating System Files

• Invoke the UTL_MAIL Package

• Write UTL_MAIL Subprograms

Dynamic SQL

• The Execution Flow of SQL

• What is Dynamic SQL?

• Declare Cursor Variables

• Dynamically executing a PL/SQL Block

• Configure Native Dynamic SQL to Compile PL/SQL Code

• Invoke DBMS_SQL Package

• Implement DBMS_SQL with a Parameterized DML Statement

• Dynamic SQL Functional Completeness

Design Considerations for PL/SQL Code

• Standardize Constants and Exceptions

• Understand Local Subprograms

• Write Autonomous Transactions

• Implement the NOCOPY Compiler Hint

• Invoke the PARALLEL_ENABLE Hint

• The Cross‐Session PL/SQL Function Result Cache

• The DETERMINISTIC Clause with Functions

• Usage of Bulk Binding to Improve Performance

Triggers

• Describe Triggers

• Identify the Trigger Event Types and Body

• Business Application Scenarios for Implementing Triggers

• Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer

• Identify the Trigger Event Types, Body, and Firing (Timing)

• Statement Level Triggers Versus Row Level Triggers

• Create Instead of and Disabled Triggers

• How to Manage, Test, and Remove Triggers?

Create Compound, DDL, and Event Database Triggers

• What are Compound Triggers?

• Identify the Timing‐Point Sections of a Table Compound Trigger

• Compound Trigger Structure for Tables and Views

• Implement a Compound Trigger to Resolve the Mutating Table Error

• Compare Database Triggers to Stored Procedures

• Create Triggers on DDL Statements

• Create Database‐Event and System‐Event Triggers

• System Privileges Required to Manage Triggers

The PL/SQL Compiler

• What is the PL/SQL Compiler?

• Describe the Initialization Parameters for PL/SQL Compilation

• List the New PL/SQL Compile Time Warnings

• Overview of PL/SQL Compile Time Warnings for Subprograms

• List the benefits of Compiler Warnings

• 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

Manage PL/SQL Code

• What Is Conditional Compilation?

• Implement Selection Directives

• Invoke Predefined and User‐Defined Inquiry Directives

• The PLSQL_CCFLAGS Parameter and the Inquiry Directive

• Conditional Compilation Error Directives to Raise User‐Defined Errors

• The DBMS_DB_VERSION Package

• Write DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text

• Obfuscation and Wrapping PL/SQL Code

Manage Dependencies

• Overview of Schema Object Dependencies

• Query Direct Object Dependencies using the USER_DEPENDENCIES View

• Query an Object’s Status

• Invalidation of Dependent Objects

• Display the Direct and Indirect Dependencies

• Fine‐Grained Dependency Management in Oracle Database 11g

• Understand Remote Dependencies

• Recompile a PL/SQL Program Unit