back to Course Categories | back | print
Oracle9iR2: Advanced PL/SQL Programming          

Duration: 3 days

Description 
This class is designed for Oracle developers who need to understand the advanced features of PL/SQL. The knowledge in this course will teach developers how to leverage the functionality in the Oracle9iR2 database and the Oracle9iR2 Application Server. Attendees will learn advanced features such as autonomous transactions, user-defined types, tuning PL/SQL, table functions, dynamic SQL native in PL/SQL, native compilation, XML, debugging and tracing complex PL/SQL programs.

Topics

  • 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

  • Attendees must understand the fundamentals of PL/SQL programming in Oracle.


 
 
 

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