Print
Oracle Advanced PL/SQL Training

Oracle 11g PL/SQL Programming Workshop

Course has been prepared by author for Zurich Financial Services (www.zurich.com) and has been successfully read for Oracle specialists in Germany (Munich).
Duration of the course: 5 days
Dates of the course: June 18, 2012 — June 22, 2012
Training class UNiORA at Bucuresti 117
Trainer: Igor Duca, OCP (http://duca.at )
Price: 1375 US Dollars w/o VAT per student
Payment - prepayment in MDL - wire transfer - exchange rate to be fixed by BNM for the day of transfer
Location of the course : Training room of UNiORA: Alba Iulia 190/1 of 26 (KVINT building: across the street - Orange Moldova)
Language — Romanian or Russian (to be selected by students), terminology - English
Maximum number of students: 11.

Audience: PL/SQL Developers and Business Analysts, Database Administrators.

Prerequisites: You must possess some programming skills to benefit from this class. For students, who have experience in PL/SQL programming, this class will be especially useful. You will acquire skills to use PL/SQL more efficiently and get your code cleaner and clearer. Also, experience with a procedural language (e.g. Java, C or COBOL), SQL and SQL*Plus is required.

To enroll, please, register.

Topic Summary

Advanced PL/SQL

Transactions

  • What is a Transaction?
  • ACID Rules. Isolation Levels
  • Statement / Transaction Level Read Consistency
  • Using DBMS_TRANSACTION
  • The Two-Phase Commit Mechanism
  • Discrete Transactions
  • Autonomous Transactions
  • Resumable Space Allocation
  • Overview of Flashback Feature
  • Flashback Query
  • Flashback Version Query
  • Flashback Transaction Query
  • Flashback Drop
  • Using DBMS_FLASHBACK
  • Using ROWSCN

PL/SQL Units

  • Anonymous PL/SQL Blocks
  • Using Subprograms
  • Advantages of Subprograms
  • Controlling Side Effects of PL/SQL Subprograms
  • Dictionary Information about PL/SQL Units
  • Subprograms Parameters
  • Packaging Subprograms
  • Overloading Subprogram Names
  • Persistent State of Package variables, cursors
  • Using Recursion
  • Forward Declarations
  • Mutually Recursive Routines
  • Invoker Rights Versus Definer Rights
  • NOCOPY Hint
  • Deterministic Clause

Exceptions

  • Exception Handling in PL/SQL
  • Retrieving the Error Code and Error Message: SQLCODE and SQLERRM
  • Read Error Stack and Call Stack using DBMS_UTILITY
  • Predefined Oracle Exceptions
  • User Defined Exeptions
  • Exception Standardisation
  • Propagation of Exceptions
  • Exception Propagation
  • Procedure RAISE_APPLICATION_ERRORSELECT INTO Statement

Triggers

  • Trigger Types
  • Event Attribute Functions
  • Trigger Design Guidelines
  • Trigger Body Restrictions
  • Avoiding Mutation Errors
  • Using FOLLOWS and PRECEDES Order Options

PL/SQL Records

  • Records Types
    Using Regards
    Manipulating Records with DML
    Records Restrictions

Oracle Collections

  • Object Datatypes in SQL: VARRAYS & Nested Tables
  • PL/SQL Tables (Associative Arrays)
  • PL/SQL VARRAYS
  • PL/SQL Nested Tables

Table Functions

  • Overview of Table Functions
  • Pipelined Table Functions
  • Parallel Execution of Table Functions

Cursors

  • Cursor Concepts and Use
  • Cursor Types
  • Cursor Attributes
  • Cursor Parameters
  • Dynamic Cursors
  • Cursor FOR LOOP
  • Modifying the Current Row
  • Cursor Expression
  • REF Cursors

BULK operations

  • Bulk Collect (Bulk Fetch)
  • Bulk DML with the FORALL statement
  • DML RETURNING into Arrays
  • Oracle 10g FORALL Improvements
  • Error Handling

Dynamic SQL

  • What is Dynamic SQL?
  • DBMS_SQL 11g New Features
  • Native Dynamic SQL
  • DBMS_SQL vs NDS

Improving PL/SQL Code

  • PL/SQL Compile-Time Warnings
  • Using the DBMS_WARNING Package
  • Code Optimization
  • Improving Performance with Caching
  • Analyzing PL/SQL Code
  • Tracing Code
  • Profiling Code

Annex1: Quick Overview of building DBMS Packages

  • UTL_COMPRESS
  • UTL_HTTP
  • UTL_FILE
  • UTL_SMTP
  • UTL_TCP
  • DBMS_OUTPUT
  • DBMS_STATS
  • DBMS_SESSION
  • DBMS_APPLICATION_INFO
  • DBMS_MAIL
  • DBMS_SUPPORT
  • DBMS_MONITOR
  • DBMS_METADATA
  • DBMS_ASSERT
  • DBMS_RANDOM
  • DBMS_CRYPTO
  • DBMS_ROWID
  • DBMS_LOCK
  • DBMS_DB_VERSION
  • DBMS_PIPE
  • DBMS_ALERT
  • DBMS_RESUMABLE
  • DBMS_JOB
  • DBMS_SCHEDULER
  • DBMS_TRACE
  • DBMS_PROFILER
  • DBMS_LDAP
  • DBMS_LOB
  • DBMS_ADDM
  • DBMS_ADVISOR
  • DBMS_DATAPUMP
  • DBMS_SPACE
  • DBMS_SYSTEM
  • DBMS_TRANSACTION
  • DBMS_UTILITY
  • DBMS_FLASHBACK