Oracle Database 11g R2 – Advanced PL/SQL Programming and Tuning

Overview

Course Description

In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c.

Audience Profile

The target audience for this textbook is all Oracle professionals. Among the specific groups for whom this textbook will be helpful are:

  • Application designers and database developers
  • Database administrators
  • Web server administrators

Prerequisites

Oracle Database 12c: SQL Fundamentals

Learning Objectives

Upon successful completion of this course, students will be able to work with Oracle database programming using the PL/SQL programming language. They will learn the syntax, structure and features of the language.

Course Outline

Course Outline

Module 1: Selection & Setup of the Database Interface
Lessons
• Considering Available Tools
• Selecting the Appropriate Tool
• Oracle Net Database Connections
• Oracle PAAS Database Connections
• Setup SQL Developer
• Setup SQL*Plus
• Setup Jdeveloper

Module 2: About Bind & Substitution Variables
Lessons
• Using SQL Developer
• Using SQL*Plus

Module 3: Choosing a Database Programming Language
Lessons
• What is Database Programming?
• PL/SQL Performance Advantages
• Integration with Other Languages

Module 4: PL/SQL
Lessons
• PL/SQL Program Structure
• LANGUAGE SYNTAX RULES
• EMBEDDING SQL
• WRITING READABLE CODE
• GENERATING DATABASE OUTPUT
• SQL*PLUS INPUT OF A PROGRAM BLOCK

Module 5: Declare Section
Lessons
• About the Declare Section
• DECLARE PRIMITIVE TYPES
• DECLARATION OPTIONS
• NOT NULL
• CONSTANT
• DATA DICTIONARY INTEGRATION
• %TYPE
• DECLARE SIMPLE USER—DEFINED TYPES
• TYPE… TABLE
• TYPE… RECORD
• EXTENDED USER—DEFINED TYPES

Module 6: Begin Section
Lessons
• About the Begin Section
• Manipulating Program Data
• Logic Control & Branching
• GOTO
• LOOP
• IF-THEN-ELSE
• CASE

Module 7: Exception Section
Lessons
• ABOUT THE EXCEPTION SECTION
• ISOLATING THE SPECIFIC EXCEPTION
• PRAGMA EXCEPTION_INIT
• SQLCODE &SQLERRM Example
• SQL%ROWCOUNT &SELECT…INTO

Module 8: Beyond the Basics: Explicit Cursors
Lessons
• ABOUT EXPLICIT CURSORS
• EXTENDED CURSOR TECHNIQUES
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Using FOR…LOOP Cursors

Module 9: Beyond the Basics: Nested Blocks
Lessons

Module 10: Beyond the Basics: Declared Subprograms
Lessons
• USING DECLARED SUBPROGRAMS
• DECLARED PROCEDURE
• DECLARED FUNCTION

Module 11: Introducing Database-Resident Program Units
Lessons
• ABOUT DATABASE—RESIDENT PROGRAMS
• PHYSICAL STORAGE & EXECUTION
• TYPES OF STORED PROGRAM UNITS
• STORED PROGRAM UNIT ADVANTAGES
• MODULAR DESIGN PRINCIPLES

Module 12: Creating Stored Procedures & Functions
Lessons
• STORED PROCEDURES & FUNCTIONS
• CREATE PROCEDURE / CREATE FUNCTION
• CREATING PROCEDURES & FUNCTIONS
• RAISE_SALARY() Procedure
• SALARY_VALID() Function
• THE PARAMETER SPECIFICATION
• DEFAULT Clause
• SYSTEM & OBJECT PRIVILEGES
• USING THE DEVELOPMENT TOOLS

Module 13: Executing Stored Procedures & Functions
Lessons
• CALLING PROCEDURES & FUNCTIONS
• UNIT TESTING WITH EXECUTE
• ANONYMOUS BLOCK UNIT TESTING
• SPECIFYING A PARAMETER NOTATION
• SQL WORKSHEET UNIT TESTING
• CALLING FUNCTIONS FROM SQL

Module 14: Maintaining Stored Program Units
Lessons
• RECOMPILING PROGRAMS
• Mass Recompilation Using UTL_RECOMP()
• DROPPING PROCEDURES & FUNCTIONS
• DROP PROCEDURE / FUNCTION
• DATA DICTIONARY METADATA
• Using USER_OBJECTS
• Using USER_SOURCE
• Using USER_ERRORS
• Using USER_OBJECT_SIZE
• Using USER_DEPENDENCIES

Module 15: Managing Dependencies
Lessons
• DEPENDENCY INTERNALS
• TRACKING DEPENDENCIES
• THE DEPENDENCY TRACKING UTILITY
• SQL DEVELOPER DEPENDENCY INFO
• DEPENDENCY STRATEGY CHECKLISTS

Module 16: Creating & Maintaining Packages
Lessons
• ABOUT PACKAGES
• CREATING PACKAGES
• MAINTAINING PACKAGES
• PERFORMANCE CONSIDERATIONS

Module 17: Advanced Package Capabilities
Lessons
• PROCEDURES
• CALLING JAVA CLASSES
• CALLING C PROGRAMS

Module 10: PL/SQL Advanced Programming & Coding Techniques
Lessons
• AUTONOMOUS TRANSACTIONS
• USING NOCOPY FOR PARAMETERS
• CHOOSING THE OPTIMUM DATA TYPE
• Avoiding Implicit Data type Conversion
• Choosing Between NUMBER And PLS_INTEGER
• About PLS_INTERGER
• Using SIMPLE_INTERGER
• CHAR Variables of Different Lengths
• CHAR Vs. VARCHAR2
• CHAR Vs. VARCHAR2 With an Equality Comparison
• USERFUL PL/SQL CODING TECHNIQUES
• HANDLING STRING LITERALS

Module 11: Influencing Oracle PL/SQL Compilation
Lessons
• PL/SQL COMPILER OPTIMIZATION
• PLSQL_OPTIMIZ_LEVEL
• CONTROLLING COMPILATION MESSAGES
• PL/SQL NATIVE EXECUTION

Module 12: Dynamic Partitioning & Parrellelization
Lessons
• ABOUT DYNAMIC PARTITIONING (CHUNKS)
• CREATING & PROCESSING CHUNKS
• CREATE_TASK()
• CREATE_CHUNKS_BY_ROWID()
• CREATE_CHUNKS_BY_NUMBER_COL()
• EXECUTE_RUN_TASK()
• TASK_STATUS()
• DROP_TASK()
• MONITORING CHUNK PROCESSING

Module 13: Application Tuning with the PL/SQL Hierarchical Profiler
Lessons
• WHAT IS THE HIERARCHICAL PROFILER?
• CONFIGURING THE PROFILER
• MANAGING PROFILER RUNS
• ANALYZING PROFILER DATA
• INTERPRETING THE RESULTS
• DBMSHP_RUNS
• DBMSHP_FUNCTION_INFO
• DBMSHP_PARENT_CHILD_INFO

Module 14: PL/SQL Debuggin with DBMS_TRACE()
Lessons
• USING THE TRACE FACILITY
• DBMS_TRACE() TO MANAGE RUNS
• EXAMINING THE TRACE DATA
• EVENT_KIND VALUES

Module 15: Protecting Against SQL Injection Attacks
Lessons
• UNDERSTANDING THE THREAT
• APPLYING COUNTERMEASURES

Module 16: Implementing Virtual Private Databases
Lessons
• UNDERSTNADING VPDS
• PREPARING FOR A VPD
• CONFIURING A VPD
• MANAGING APPLICATION CONTEXTS
• Using SYS_CONTEXT()
• MANAGING POLICIES & SECURITY RULES

Register for Course

Tuition
Available Registrations: Unlimited
The Tuition ticket is sold out. You can try another ticket or another date.

Cost

$2,650.00

Course Code

FRCORA-7

Course Duration

5

Instructions Method

Instructor Led
Category
REGISTER