back to Course Categories | back | print
Oracle10g: Programming with PL/SQL          

Duration: 5 days

Description 
PL/SQL is still the core programming language in Oracle. Oracle10g significantly expands the performance and features of PL/SQL in the database. This class will focus on developing PL/SQL programming skills for Oracle developers. Students will learn the fundamentals of the PL/SQL programming language. Students will write stored procedures, functions, packages and triggers and implement complex business rules in Oracle. Features such as dynamic obfuscation, dynamic SQL, invoker/definers rights and performance tuning will be covered. Numerous exercises and labs will help students develop skill with the PL/SQL language. This intense five-day class combines the Introduction and Advanced PL/SQL Programming classes.

Topics

  • PL/SQL in the Oracle10g environment
  • Features of PL/SQL
  • The Block Structure
  • Language Rules
  • Executing PL/SQL from SQL*Plus
  • Variables and Constants
  • Identifiers
  • Customizing SQL*Plus for PL/SQL Scripts
  • SELECT INTO Statement
  • Using Bind Variables
  • Interactive PL/SQL Programs
  • Oracle Datatypes are supported in PL/SQL
  • Numeric Datatypes
  • Datatype Conversion
  • Implicit and Explicit Conversion
  • %TYPE and %ROWTYPE
  • Subtypes
  • Subtype Declarations
  • Labels
  • Variable Scope and Visibility Rules
  • SQL and PL/SQL Reserved Words
  • PL/SQL Reserved Words
  • Direction of PL/SQL
  • External Routines and Java Stored Procedures
  • Control Flow
  • Operators
  • Built-In Functions
  • Handling Exceptions
  • Working with Exceptions
  • SQLCODE and SQLERRM Functions
  • Predefined PL/SQL Exceptions
  • User-Defined Exceptions
  • Working with Exceptions
  • The Procedure Raise_Application_Error
  • IF Statements
  • Generic LOOP
  • Working with NULL
  • LOOP Labels
  • FOR LOOP
  • WHILE LOOP
  • GOTO Statement
  • Working with Cursors
  • Working with Cursors
  • Implicit Cursors and Attributes
  • Explicit Cursors
  • Explicit Cursor Attributes
  • Using Subqueries with Cursors
  • Using Cursor FOR LOOPs
  • Using %ROWTYPE with Cursors
  • Using Aliases and Subqueries
  • Using Aliases and Subqueries (FOR LOOP)
  • Using Parameters with Explicit Cursors
  • Modifying Data using Explicit Cursors
  • Managing Transactions in PL/SQL Programs
  • Procedures and Functions
  • Working with Subprograms
  • Named Subprograms
  • Software Evolution
  • Stored Procedures
  • Parameters
  • NOCOPY Compiler Directive
  • Parameter Default Values
  • Positional and Named Notation
  • Executing a Stored Procedure
  • Compiling and Debugging Stored Procedures
  • Compiling a Stored Procedure
  • Stored Function Syntax
  • Executing a Function from SQL*Plus
  • The RETURN Statement
  • Advantages of Packages
  • Packages
  • What can a Package Contain?
  • Components of a Package
  • Package Specification
  • Package Body
  • Defining Package Components
  • Package Scope
  • Public Scope
  • Private Scope
  • Accessing Package Components
  • Privileges
  • Managing Side Effects
  • Defining Subprograms within another Subprogram
  • Forward Declarations
  • Overloading
  • Restrictions on Overloading
  • Restrictions on Packaged Variables
  • Triggers
  • Purpose of Triggers
  • Trigger Guidelines
  • Table Triggers
  • Firing Order of Table Triggers
  • Table Trigger Syntax
  • ALTER and DROP Trigger Commands
  • Boolean Functions
  • Reference Variables for ROW Triggers
  • ROW Trigger Examples
  • Information about Triggers
  • WHEN Clause
  • Restrictions
  • Database Triggers
  • Event Functions
  • Resource Manager Events
  • Client Events
  • Schema Triggers
  • INSTEAD-OF Triggers
  • object-relational types
  • Autonomous Transactions
  • Packages and Triggers
  • Autonomous Triggers
  • Autonomous PL/SQL Blocks
  • Transaction Visibility
  • Definer’s Rights
  • Invoker’s Rights
  • Using the AUTHID Clause
  • Resolving External References
  • PL/SQL Wrappers
  • External Procedures
  • External Procedure Architecture
  • Alias Library Object
  • Call Specifications
  • Mapping a Procedure to an External Procedure
  • Mapping a Function to an External Procedure
  • Issues with 3GL Development
  • Issues with Relational Database Development
  • Object-Relational Development
  • Goals of Object-Relational Development
  • Functional versus Object Hierarchies
  • Software Evolution
  • User-defined types and objects
  • Rules on Working with User Defined Types
  • Inheritance
  • Single versus Multiple Inheritance
  • Polymorphism
  • With and Without Polymorphism
  • Different Object Methodologies
  • What is UML?
  • Visual Models
  • Attributes
  • Different Types of Methods
  • Object-Relational Databases
  • Object-Relational Features
  • Oracle Object-Relational Features
  • Oracle Datatypes
  • Abstract Datatypes
  • Data Dictionary Views
  • The CREATE TYPE Command
  • Object Tables
  • Type Methods
  • The Constructor Method
  • Working with User Defined Datatypes
  • Modifying Data in Object Tables using User Defined Datatypes
  • Working with Object Tables
  • Object Identifiers
  • Defining a REF
  • Updating REF Values
  • INSERTING REF Values
  • Dangling REFS
  • The DEREF Operator
  • ROWIDs
  • DBMS_ROWID Package
  • Different Types of Rowids
  • Object Methods
  • Different Types of Methods
  • Create Type Body Command
  • Executing Object Methods
  • Differences between Packages and Objects
  • DDL Type Commands
  • MAP Method
  • ORDER Method
  • MAP versus ORDER Method
  • STATIC Methods
  • Objects and Nulls
  • LOBS
  • Records
  • Creating a PL/SQL Record
  • PL/SQL2 Tables
  • Defining PL/SQL2 Tables
  • Table Attributes
  • PL/SQL8 Tables
  • Instantiating PL/SQL Tables
  • Create a Type Specification
  • CAST Operator
  • Queries on Nested Tables
  • Dynamic SQL
  • Executing Dynamic SQL Statements in PL/SQL
  • Advantages of Dynamic SQL
  • Advantages of DBMS_SQL
  • DBMS_SQL Package
  • Advantages of Native Dynamic SQL in PL/SQL
  • More on the Execute Immediate Statement
  • Bind Variables
  • Defining Modes for Bind Variables
  • Executing DML, DCL, and DDL Statements
  • Executing Dynamic Multi-row Queries
  • Using Schema objects with Dynamic SQL
  • Cursor Attributes
  • Assigning Nulls to Bind Variables
  • Issues When Executing Subprograms
  • Debugging
  • Problem Analysis
  • Handling Exceptions
  • Continuing After an Exception
  • Using an Inner Block to Continue after an Exception
  • Inner Block Approach
  • Function Approach
  • Locator Variables
  • Temporary Tables
  • Storing Debugging Information
  • The DBMS_TRACE Package
  • DBMS_TRACE Constants
  • Managing the Volume of Tracing Information
  • The DBMS_DEBUG Package
  • Procedures in DBMS_DEBUG
  • Setting up a Debug Session
  • Working with Object Types and Collections
  • Bulk Binds
  • Bulk Collect Into Clause
  • Use the RETURNING Clause
  • NOCOPY Hint
  • Memory and PL/SQL
  • Pinning PL/SQL Programs
  • KEEP Procedure
  • What Do I to PIN?
  • Relative Sizes of Code to Pin
  • Serially Reusable Packages
  • Optimizing Integer Operations
  • Avoid NOT NULL Constraints
  • Rephrase Conditional Control Statements
  • DBMS_PROFILER
  • Profiler Tables
  • Table PLSQL_PROFILER_DATA
  • Type Evolution
  • Inheritance
  • Super and Subtypes
  • Restrictions on Overriding
  • Dynamic Method Dispatch
  • Substitutability
  • The TREAT Function
  • More on Inheritance
  • NOT INSTANTIABLE
  • Object View Hierarchies
  • IS OF Operator
  • The SYS_TYPEID Function
  • Object Support in Programmatic Environments
  • CASE Expressions and Statements
  • Searched CASE expression
  • The WHEN Clause
  • CASE Statement
  • Searched CASE statement
  • TABLE FUNCTIONS
  • Pipelined versus Non-pipelined
  • Returning Results from Table Functions
  • New SQL Datatypes
  • Performing DML Operations on Table Functions
  • Parallelizing Table Functions
  • Enhancements to Bulk Operations
  • Bulk FETCH
  • Bulk FORALL with EXECUTE IMMEDIATE
  • Errors in DML Operation
  • Bulk FORALL with EXECUTE IMMEDIATE
  • ADVANCED QUEUEING – New Features
  • JMS Enhancements
  • Native Compilation of PL/SQL Code
  • Steps to Compile PL/SQL Code
  • Better Integration of LOB Datatypes
  • Multilevel Collections
  • MERGE STATEMENT 17-13
  • New PL/SQL Supplied Packages
  • Cursor Subquery
  • XML Data Type
  • INSERTING INTO AN XML COLUMN
  • Retrieving an XML Column
  • Updating an XML column
  • XMLTYPE FUNCTIONS
  • Common Constructs used in Xpath Expressions
  • XMLTYPE MEMBER FUNCTIONS
  • DBMS_XMLGEN
  • SYS_XMLGEN
  • SYS_XMLAGG
  • Accessing Metadata
  • The DBMS_METADATA Package
  • DBMS_METADATA Object Types
  • Customizing DDL Output
  • GET_XML AND GET_DDL

Prerequisites

  • A solid understanding of SQL and 3GL programming is required. It is recommended the student take the Oracle10g: SQL Programming course or understand Oracle10g SQL features.


 
 
 

Trubix Inc. P.O. Box 2235 Littleton, CO 80161 | 1.866.4-TRUBIX • 303.730.6800
email: info@trubix.com
....................................................................................................................................................................

Training Service | Consulting / Mentoring | Public Schedule | Course Listings | Tru iLearning | About Us | Contact Us
Oracle DBA Classes | Oracle Dev Classes | Oracle App Server Classes | Linux / Unix Classes
SQL Server / .NET / Tools / DB2 Classes / Public Classes

 

 
all content property © 2006, 2007Trubix Inc.| site design by Art Z design