Oracle Database 11g Programming with PL/SQL
Course number: OD11-CGI002
A good Oracle database administrator must gain and maintain a mastery of his or her environment, its physical structures, and its maintenance routines. An administrator must understand the data being stored well enough to tune and improve performance, to anticipate and prevent data failure, and to recover lost data should a failure occur. In this course, you will develop an advanced understanding of complex Oracle database concepts, maintenance and recovery routines, and database management. You have an Oracle installation and need to broaden your knowledge of advanced Oracle administrative functionality so that you can maintain the healthiest possible database.
You will use PL/SQL to create programs with program units such as procedures, functions, packages, and database triggers to enhance data manipulations.
Upon successful completion of this course, students will be able to:
- write a simple PL/SQL program to familiarize themselves with the fundamentals and structure of PL/SQL blocks.
- create a PL/SQL program using variables and lexical units.
- use control structures to help them incorporate decision-making approach to programming.
- handle PL/SQL exceptions.
- work with PL/SQL cursors to embed SQL in PL/SQL code or call PL/SQL functions within SQL structures.
- create PL/SQL subprograms.
- use PL/SQL collections and records to enhance database performance during data processing.
- develop and use PL/SQL packages.
- create triggers to automatically trigger validation procedures when a table is modified.
- use the PL/SQL compiler.
- manage dependencies.
- use dynamic SQL to handle changes in data definitions without recompiling.
This course is designed for IT Professionals with programming experience. The target student will also have experience in using SQL with Oracle 10g or 11g.
Students enrolling in this class should have knowledge of working with Oracle databases. To ensure your success, we recommend that you first complete the following Element K courses or have equivalent knowledge.
- Oracle Database 11g: Introduction, and
- Oracle Database 11g: SQL Fundamentals I & II
- Topic 1A: Examine PL/SQL Basics
- Topic 1B: Write a Simple PL/SQL Program Block
- Topic 2A: Use PL/SQL Variables and Data Types
- Topic 2B: Use PL/SQL Lexical Units
- Topic 3A: Use PL/SQL Operators and Expressions
- Topic 3B: Use Conditional Statements
- Topic 3C: Use Iterative Statements
- Topic 4A: Handle System-Defined Exceptions
- Topic 4B: Handle User-Defined Exceptions
- Topic 5A: Manage Data Using Cursors
- Topic 5B: Retrieve Data Using the Cursor with Looping Mechanisms
- Topic 6A: Use Stored Subprograms
- Topic 6B: Create User-Defined Subprograms
- Topic 7A: Work with Collections
- Topic 7B: Create Record Variables
- Topic 8A: Create Packages
- Topic 8B: Work with Product-Specific Packages
- Topic 9A: Create a Simple Trigger
- Topic 9B: Create a Compound Trigger
- Topic 10A: Apply Conditional Compilation
- Topic 10B: Hide PL/SQL Code
- Topic 11A: Work with Trigger Dependencies
- Topic 11B: Manage Package Dependencies
- Topic 12A: Use Native Dynamic SQL
- Topic 12B: Work with the DBMS_SQL Package