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