Oracle 11g Programming Training Course

Introduction to Oracle 11g PL/SQL Programming – ORA503 – 3 Days

This Oracle PL/SQL training class is designed to give students a hands-on Oracle PL/SQL learning experience.  This course is designed with 40% lecture and demos and 55% hand on labs and Q&A.   Dozens on labs and examples are delivered and student for from easy SQL to more complex PL/SQL coding.

Introduction or Oracle PL/SQL training course

Intro to PL/SQL Training

In this live, instructor-led Oracle PL/SQL training course students learn the fundamentals of PL/SQL which is Oracle’s Procedural Language for SQL. PL/SQL is Oracle’s database programming language for creating stored procedures, functions, database triggers, and object methods. PL/SQL can be used for implementing business rules, computing algorithms, manipulating data, and for stand-alone programs. In the Oracle 11g version release has greatly enhanced the features and functionality of PL/SQL.

Students learn how to write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students will learn programming, management, and security issues of working with PL/SQL program units. Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.

Oracle 11g PL/SQL Training – ORA503 – 3 Days ENROLL NOW - Individuals & Small GroupsGet a Group Quote

In this three day live instructor-led Oracle PL/SQL training course students learn the fundamentals of PL/SQL which is Oracle’s Procedural Language for SQL. PL/SQL is Oracle’s database programming language for creating stored procedures, functions, database triggers, and object methods.

Multi-Enrollment Discounts Available at Check-Out
Location Instructor-Led Online
Hosted By Firebox Training
PO BOX 334
Golden CO 80402
303-416-3056

June 4, 2014 - June 6, 2014
8:00 AM - 4:00 PM
Super Nova - Offer Ends: 04/25/2014$1,695
Fire Starter - Offer Ends: 05/16/2014$1,850
Last Chance - Offer Ends: 05/28/2014$1,950

Course ID: ORA503    Duration: 3 days
Audience: Application developers and database administrators

Introduction to Oracle 11g PL/SQL Programming Training Course Topics:

Triggers

  • Beyond Declarative Integrity
  • Triggers
  • Types of Triggers
  • Trigger Sequencing
  • Row-Level Triggers
  • Trigger Predicates
  • Trigger Conditions
  • Using Sequences
  • Cascading Triggers and Mutating Tables
  • Generating an Error
  • Maintaining Triggers

PL/SQL Variables and Datatypes

  • Anonymous Blocks
  • Declaring Variables
  • Datatypes
  • Subtypes
  • Character Data
  • Dates and Timestamps
  • Date Intervals
  • Anchored Types
  • Assignment and Conversions
  • Selecting into a Variable
  • Returning into a Variable

PL/SQL Syntax and Logic

  • Conditional Statements – IF/THEN
  • Conditional Statements – CASE
  • Comments and Labels
  • Loops
  • WHILE and FOR Loops
  • SQL in PL/SQL
  • Local Procedures and Functions

Stored Procedures and Functions

  • Stored Subprograms
  • Creating a Stored Procedure
  • Procedure Calls and Parameters
  • Parameter Modes
  • Named Parameter Notation
  • Default Arguments
  • Creating a Stored Function
  • Stored Functions and SQL
  • Invoker’s Rights

Exception Handling

  • SQLCODE and SQLERRM
  • Exception Handlers
  • Nesting Blocks
  • Scope and Name Resolution
  • Declaring and Raising Named Exceptions
  • User-Defined Exceptions

Records, Collections, and User-Defined Types

  • Record Variables
  • Using the %ROWTYPE Attribute
  • User-Defined Object Types
  • VARRAY and Nested TABLE Collections
  • Using Nested TABLEs
  • Using VARRAYs
  • Collections in Database Tables
  • Associative Array Collections
  • Collection Methods
  • Iterating Through Collections
Cursors

  • Multi-Row Queries
  • Declaring and Opening Cursors
  • Fetching Rows
  • Closing Cursors
  • The Cursor FOR Loop
  • FOR UPDATE Cursors
  • Cursor Parameters
  • The Implicit (SQL) Cursor
  • Bulk Operations
  • Bulk Binding
  • BULK COLLECT Clause
  • FORALL Statement
  • FORALL Variations
  • Bulk Returns
  • Bulk Fetching with Cursors

Using Packages

  • Packages
  • Oracle-Supplied Packages
  • The DBMS_OUTPUT Package
  • The DBMS_UTILITY Package
  • The UTL_FILE Package
  • Creating Pipes with DBMS_PIPE
  • Writing to and Reading from a Pipe
  • The DBMS_METADATA Package
  • XML Packages
  • Networking Packages
  • Other Supplied Packages

Creating Packages

  • Structure of a Package
  • The Package Interface and Implementation
  • Package Variables and Package State
  • Overloading Package Functions and Procedures
  • Forward Declarations
  • Strong REF CURSOR Variables
  • Weak REF CURSOR Variables

Working with LOBs

  • Large Object Types
  • Oracle Directories
  • LOB Locators
  • Internal LOBs
  • LOB Storage and SECUREFILEs
  • External LOBs
  • Temporary LOBs
  • The DBMS_LOB Package

Maintaining PL/SQL Code

  • Privileges for Stored Programs
  • Data Dictionary
  • PL/SQL Stored Program Compilation
  • Conditional Compilation
  • Compile-Time Warnings
  • The PL/SQL Execution Environment
  • Dependencies and Validation
  • Maintaining Stored Programs

Appendix A: Dynamic SQL

  • Generating SQL at Runtime
  • Native Dynamic SQL vs. DBMS_SQL Package
  • The EXECUTE IMMEDIATE Statement
  • Using Bind Variables
  • Multi-row Dynamic Queries
  • Bulk Operations with Dynamic SQL
  • Using DBMS_SQL
  • DBMS_SQL Subprograms

Appendix B: PL/SQL Versions, Datatypes, and Language Limits

Appendix C: Oracle 11g Supplied Packages