Oracle Database: Develop PL/SQL Program Units Ed 2

Course code

Orcl_Db_Develop

Duration

3 Days, 24 Acad. Hours

Download pdf

Course Overview

Objectives

Prerequisites

Course Outline

Course Overview

Course overview

This course is designed for developers with basic PL/SQL and SQL language skills. Students learn to develop, execute, and manage PL/SQL stored program units such as procedures, functions, packages, and database triggers. Students also learn to manage, PL/SQL subprograms and triggers.

Objectives

Course Objectives

Create triggers to solve business challenges
Manage dependencies between PL/SQL subprograms
Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions
Use the compiler warnings infrastructure
Create, use, and debug stored procedures and functions
Design and use 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 conditional PL/SQL compilation and obfuscate (hide) code

Prerequisites

Suggested Prerequisite

Familiarity with programming languages
Familiarity with data processing concepts and technique

Required Prerequisite

Oracle Database 12c: Introduction for Experienced SQL Users

Audience

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

Course Outline

Course Topics

1. Introduction

PL/SQL development environments available in this course
Using the SQL Worksheet
Executing SQL Statements
Creating and Executing Anonymous Blocks
Course Objectives, Course Agenda and Appendixes Used in this Course
Full Human Resources (HR) Schema
Online Oracle Database 12c SQL and PL/SQL documentation
Working With Script Files

2. Working with Oracle Database Exadata Express Cloud Service

Connecting to Exadata Express Database using Database Clients
Using SQL Developer to work with Exadata Express Database
Using SQLcl to work with Exadata Express Database
Using SQL*Plus to work with Exadata Express Database
Overview of Oracle Database Exadata Express Cloud Service
Accessing Cloud Database using SQL Workshop

3.Creating Stored Procedures

Working with procedures
Using formal and actual parameters
Handling exceptions in procedures
Passing parameters using the positional, named, or combination techniques
Uses and benefits of procedures
Identify the available parameter-passing modes
Viewing the procedure information
PL/SQL blocks and subprograms

4. Creating Functions and Debugging Subprograms

Working with Functions
Creating Stored Functions
Using User-Defined Functions in SQL Statements
Defining and executing PL/SQL functions in SQL statements
Identifying the Advantages of Using Stored Functions in SQL Statements
The Difference Between Procedures and Functions
Using a PL/SQL Function in the SQL WITH Clause
Restrictions When Calling Functions from SQL statements

5. Creating Packages

Developing a PL/SQL Package
Invoking the Package Constructs
Removing a Package
Visibility of a Package’s Components
Components of a PL/SQL Package
Creating and Using Bodiless Packages
Using PL/SQL Packages
Creating the Package Specification and Package Body

6. Working With Packages

Controlling Side Effects of PL/SQL Subprograms
Initializing Packages
Using Forward Declarations to Solve Illegal Procedure Reference
Using Package Functions in SQL and Restrictions
Persistent State of Package Variables and Cursors
Using PL/SQL Tables of Records in Packages
Overloading Subprograms
Persistent State of Packages

7. Using Oracle-Supplied Packages in Application Development

Using the UTL_MAIL Package
Using Oracle-Supplied Packages
Examples of Some of the Oracle-Supplied Packages
Using the UTL_FILE Package to Interact With Operating System Files
Working of DBMS_OUTPUT Package

8. Using Dynamic SQL

Introduction to Dynamic SQL
Dynamic SQL using DBMS_SQL package
Working With Dynamic SQL
Dynamic SQL with mock up application
The Execution Flow of SQL
Using Native Dynamic SQL (NDS)
When Do You Need Dynamic SQL?
Using BULK COLLECT and FORALL

9. Creating Triggers

Different types of triggers
Creating database triggers
Database triggers and their use
Removing database triggers
Database trigger firing rules

10. Creating Compound, DDL, and Event Database Triggers

Creating triggers on DDL statements
Displaying information about triggers
Creating triggers on system events
Compound triggers
Mutating tables

11. Design Considerations for PL/SQL Code

Grant Roles to PL/SQL Packages and Standalone Stored Subprograms
Writing PL/SQL code that uses local subprograms
Standardizing exceptions with an exception package
Using the PARALLEL ENABLE hint for optimization
Using the NOCOPY compiler hint to pass parameters by reference
Using the AUTONOMOUS TRANSACTION pragma
Standardizing constants with a constant package
Describing the differences between invoker rights and definer rights

12. Using PL/SQL compiler

Viewing the Compiler Warnings
Viewing the Current Setting of PLSQL_WARNINGS
Guidelines for using PLSQL_WARNINGS
Conditional Compilation
Using the PL/SQL Compile Time Warnings
Using the PL/SQL Compiler with initialisation parameters

13. Managing Dependencies

Managing local and remote procedural dependencies
Predicting the effect of changing a database object
Tracking procedural dependencies with dictionary views
Dependent and referenced objects

Back to trainings (Oracle)

Request the training

Oracle Database: Develop PL/SQL Program Units Ed 2

Course code:

Orcl_Db_Develop

Duration:

3 Days, 24 Acad. Hours

Apply

Download pdf