Training

 

 

Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley
Columbia

locations map

Calgary

550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2

Toronto

821A Bloor Street West
Toronto, ON
M6G 1M1

Vancouver

409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA
19046

Other Locations

Dallas, TX
Miami, FL

Home > Training > Oracle 11g > Oracle 10g Advanced PL/SQL Programming Training

Oracle 10g Advanced PL/SQL Programming Training

Course#: WA2081

This advanced course helps PL/SQL programmers take advantage of language features, advanced techniques, and packages and facilities provided by Oracle® to develop and tune efficient and effective PL/SQL subprograms. 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. 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.

Audience:

Programmers and Database Administrators responsible for the development or maintenance of application systems which access an Oracle database using PL/SQL

Prerequisites:

Previous experience/knowledge of Oracle SQL & PL/SQL basics

Duration:

2 Days

Outline of WA2081 Oracle 10g Advanced PL/SQL Programming Training

1. Course Introduction

  • Course Objectives
  • Course Overview
  • Using the Workbook
  • Suggested References

2. The PL/SQL Execution Environment

  • The Server Process
  • PL/SQL Execution
  • The PL/SQL Compiler
  • Compiler Optimization
  • SQL - Parse
  • SQL - Execute and Fetch
  • Server Memory
  • Latches
  • Locks

3. Advanced Cursors

  • Cursor Types
  • Cursors and Storage
  • Spanning Commits Across FETCHes
  • Dynamic SQL in PL/SQL
  • Bulk Operations
  • Bulk Returns
  • Limiting Results
  • Cursor Parameters
  • Cursor Variables
  • Strong and Weak Cursors
  • Using Cursor Variables
  • Cursor Type Errors
  • Cursor Subqueries

4. 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 for DML and DDL
  • Using DBMS_SQL for Queries
  • Retrieving Meta Information with DBMS_SQL

5. Object-Oriented Oracle

  • Introducing Object-Oriented Oracle
  • Defining Object Types and Tables in SQL
  • Querying and Modifying Object Data
  • Object Method
  • Inheritance
  • Type Evolution
  • Object Views
  • Object Types in PL/SQL
  • REF Pointers
  • Object Functions and Operators

6. Tuning PL/SQL

  • PL/SQL vs SQL
  • PL/SQL Performance Tips
  • Tuning Goals
  • Monitoring Wait Events
  • DBMS_PROFILER
  • DBMS_TRAC
  • Execution Plans
  • Interpreting Explain Plan Results
  • Execution Plan Details
  • Trace Files
  • TKPROF
  • Using trcsess
  • DBMS_APPLICATION_INFO

7. Debugging and Error Handling

  • Exception Management
  • Exception Propagation
  • User-Defined Exceptions
  • Exception Error Messages
  • Stack Management
  • Debugging with DBMS_OUTPUT
  • Debugging with a Table
  • Using UTL_FILE
  • Using DBMS_DEBUG
  • SQL Developer
  • Avoiding Bugs

8. Advanced Programming Topics

  • Autonomous Transactions
  • Invoker's Rights
  • Fine-Grained Access Control with DBMS_RLS
  • Creating Pipes with DBMS_PIPE
  • Writing to and Reading from a Pipe
  • Table Functions
  • Pipelined Table Functions
  • Enabling parallel execution
  • DETERMINISTIC Functions

9. Interfacing with External Code

  • External Programs and Procedures
  • External Procedure Architecture
  • Configure Oracle For External Procedures
  • Creating a java Stored Procedure
  • Security and External Programs
  • The Job Scheduler
  • Manage and Drop External Jobs
  • Native Compilation of PL/SQL Code
  • The Oracle Call Interface (OCI and OCCI)
  • Pro*C and Pro*C++
  • Using Pro*C and Pro*C++
  • Perl DBI/DBD Architecture
  • Perl and Stored Procedures
  • ODBC
  • Using ODBC
  • JDBC

10. Working With XML

  • Databases and XML
  • Schema Validation
  • Unstructured and Structured Storage
  • The XMLType Datatype
  • XPath Expressions
  • Extracting XML Data
  • Generating XML
  • XMLQuery
  • XMLType Views
  • Oracle XML DB Repository
We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
*Your name:

*Your e-mail:

*Phone:

*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.

 

Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.