Course Description
Oracle 11g PL/SQL Programming Part 2 online training picks up right where Part 1 completes and continues to guide you through more intermediate PL/SQL topics. Through hands-on exercises, you learn to handle all types of runtime errors, use explicit cursor processing including cursor FOR loops, create and use stored procedures and functions, and design and use PL/SQL packages as well as understand the benefits of using packages. This Oracle programming course teaches you to manage dependencies between these PL/SQL stored units, and understand and manage security for them as well. Also covered in this Oracle training is learning how to automate processing by creating triggers to solve business requirements including 11g compound triggers.
In order to take this course you need to have taken Oracle 11g PL/SQL Programming Part 1 and have experience in some high-level programming such as SQL.
In This Course You Will Learn:
- Describe the features and syntax of PL/SQL
- Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
- Handle runtime errors
- Design PL/SQL anonymous block that execute efficiently
- Create and use stored procedures and functions
- Design and use PL/SQL packages to group and contain related constructs
- Manage dependencies between PL/SQL subprograms
- Use conditional compilation to customize functionality in a PL/SQL block
- Create overloaded package subprograms
- Use the Oracle supplied PL/SQL packages to generate screen output, file output, and schedule jobs
- Write dynamic SQL for coding flexibility
- Understand security for stored PL/SQL units
- Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions
- Create triggers to solve business requirements including 11g compound triggers
- Use the 11g PL/SQL function cache
Prerequisites
You need to have equivalent experience and have taken Oracle 11g PL/SQL Programming Part 1 or taken the following GogoTraining courses:
Outline
Module 00: Oracle 11g PL/SQL Programming Part 2 online training - Course Introduction
Module 01: Cursor Basics
- Learn How Oracle uses Cursors to Process SQL
- Identify when to use an Explicit Cursor
- Describe an Implicit Cursor
- Use Cursor OPEN, FETCH, and CLOSE Statements
- Identify and Use Cursor Attributes
- Use Parameters with Cursors
- Use Cursor FOR Loops
Module 02: Cursor Basics Exercise and Exercise Review
Module 03: Advanced Cursor Topics
- Using %ROWTYPE
- Cursor FOR Loops
- Cursors with Parameters
Module 04: Advanced Curser Topics Part 2
- Using "Where Current of" Option
- Exercise Cursors
- Exercise Review
- What Should I Know about Cursor Processing?
Module 05: Stored Procedures and Functions Basics
- Create Stored Procedures
- Create Stored Functions
- Use a Function in a SQL Statement
- Shared SQL Area
- Pass Parameters to Procedures and Functions
- Locate Source Code in the Database
Module 06: Stored Procedures and Functions Basics Exercise and Exercise Review
Module 07: Passing Parameters to Stored Procedures and Functions
- Using Parameters
- Formal Parameter Modes
Module 08: Passing Parameters to Stored Procedures and Functions Exercise and Exercise Review
Module 09: Dependencies and Security
- Source Code and Dependencies
- security for Stored Program Units
- Implementing Invoker's Rights
- DETERMINISTIC Functions
Module 10: Stored Procedure and Functions Exercise and Exercise Review
- What Should I Know about Stored Procedures and Functions?
Module 11: Trigger Basics
- Triggers
- Define Characteristics of Database Triggers
Module 12: Trigger Basics Part 2
- Row vs. Statement Triggers
- Trigger Types
- Understand the Difference between Statement Level and Row Level Triggers
- Identify Restrictions for Triggers
Module 13: Advanced Trigger Concepts
- Use Trigger Predicates
- Retrieve Trigger Definitions from the Data Dictionary
- Enable and Disable Triggers
- Execution Order of Triggers- Using FOLLOWS clause Compound Triggers (11g)
- Other Events and Triggers
Module 14: Triggers Exercise and Exercise Review
- What Should I Know about Triggers?
Module 15: PL/SQL Packages
- Identify Advantages of Using Packages
- Define a Package Specification
- Define a Package Body
Module 16: PL/SQL Packages Part 2
- Implement Public and Private Elements
- Understand Persistent State of Packaged Objects
- Execute Procedures and Functions in a Package
Module 17: Packages Exercise and Exercise Review
- What Should I Know about Packages?
Certification
Oracle 10g/11g PL/SQL programming Part 2 is part of a two-course online training series that helps you to prepare for one of the following exams: 1Z0-147 Program with PL/SQL or 1Z0-144 Oracle Database 11g: Program with PL/SQL. When you pass one of these exams, you have completed a portion of the requirements towards the Oracle PL/SQL Developer Certified Associate certification.
For more information about certification, requirements and fees, click on the link found in Related Links.