• PL/SQL Mosaic
• Everything You Need to Know About Collections, But Were Afraid to Ask
• Making the Most of PL/SQL Error Management Features
• Hands-on Collections
• Gee Whiz! Oracle10g New Features for PL/SQL
• 21st Century PL/SQL
• The Brave New World of PL/SQL
• This Old Program, These Old Programmers - Refactoring for PL/SQL Developers
• Oracle PL/SQL Advanced Techniques

Download Now! (2.3 MB, Updated: February 8, 2008) 

A two-day exploration of the Oracle PL/SQL language, including coverage of Oracle Database 11g's new features for PL/SQL! In honor of the amazing, mosaic-like sidewalks of Prague (click here for more on Steven's delight in these sidewalks), Steven has named this course the "PL/SQL Mosaic." It covers a wide variety of topics most interesting to PL/SQL developers, including: Oracle Database 11g features, including the function result cache and compound triggers. It also covers object types, managing files, testing PL/SQL code and more.

Download Now! (1 MB, Updated: November 3, 2006) 

PL/SQL collections ­ array-like structures ­ are at the heart of many of the latest innovations and optimizations available in the PL/SQL language. Collections were first introduced into PL/SQL in Oracle7, and since then Oracle has come to rely increasingly on collections to support exciting and critical new features. For example...

• You can improve the performance of multirow SQL executed from within PL/SQL by an order of magnitude or more by using BULK COLLECT and FORALL. Both of these statements populate or read from collections.
  
• Logic for Oracle-based data warehouses can be greatly simplified and optimized through the use of table functions: functions that return collections and can be called in the FROM clause of a query.
  
• Complex searching algorithms can be replaced with much more straightforward logic by taking advantage of multilevel and string-indexed collections.

The bottom line is that it is impossible in the 21st century to write high performance, optimized, easily maintained PL/SQL code unless you rely heavily and consistently on collections. After studying this presentation, you will gain a solid foundational understanding of collections and how to apply them to solve critical PL/SQL challenges.

Download Now! (700 KB, Updated: November 3, 2006) 

Every well-crafted application follows a standard, consistent process for handling, raising and reporting errors. PL/SQL offers a powerful, flexible exception handling architecture, but it does have some limitations. Steven will show you how to take advantage of the latest error management features, work around those limitations, and rely on generic, reusable programs to raise, handle and log exceptions.

Oracle PL/SQL offers a wealth of features and possibilities when it comes to managing errors. Did you know, for example, that in Oracle Database 10g, you can use the DBMS_ERRLOG package to continue past DML errors and log those errors very efficiently? And then there is the totally wondrous DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, which allows us (finally) to obtain the line number on which the error was raised. Of course, there are clouds inside the silver lining. The EXCEPTION datatype in PL/SQL, for example, leaves much to be desired. So much to learn, so many ways to improve our code! 
 

Download Now! (1.2 MB, Updated: 11/5/04)

Collections are among the most important and least intuitive of data structures in the world of the Oracle PL/SQL language. First introduced in Oracle7 as PL/SQL tables, collections now come in three flavors (associative arrays, nested tables and varrays) and are used in some of the most interesting and critical new features of PL/SQL, from bulk processing (BULK COLLECT and FORALL) to table functions.

This two-day course provides detailed instruction on all aspects of working with collections. Each lesson will be followed by exercises to ensure that students understand both the concepts and techniques necessary to becoming an expert at working with collections.

This download contains the following files:

• Hands-on collections.ppt - The Powerpoint presentation
• hands-on collections intro.doc - Cover document with copyright information and table of contents to the Powerpoint document.
• hoc_exercises.sql - A Quest CodeGen Utility snapshot of the exercises for this course. Use the Admin->Import facility of Qnxo to install these exercises, if you have Qnxo installed. 
• hoc_exercises.html - An HTML document containing all the course exercises and solutions
• hoc_exercises_no_solutions.html - An HTML document containing all the course exercises without the solutions

Don't forget to also download the zip file containing a full set of supplementary scripts referenced in the presentation.

Download Now! (200 K, Updated: February 14, 2005)

Oracle10g might let the DBAs off the hook with automated management, but us developers still have our work cut out for us. Fortunately, Oracle offers some most excellent new toys for PL/SQL programmers and DBAs, including powerful set operations on nested tables, a job scheduler you can actually come to love, and an optimizing compiler. This presentation covers the most useful and intersting new features in Oracle10g PL/SQL.

Download Now! (.8 MB, Updated: February 8, 2008)

Oracle PL/SQL has been around since the early 1990s, but it has been enhanced in many fundamental and important ways since then. Many PL/SQL developers still rely too heavily on the core PL/SQL functionality found in Oracle7 and Oracle8. If you feel that you are falling behind the PL/SQL learning curve and want to make sure that you are using the latest and greatest PL/SQL has to offer, you should attend "Oracle10g Database PL/SQL: PL/SQL Programming for the 21st Century."

This seminar, prepared specially for Oracle Scandinavia by Steven Feuerstein, showcases the new features of Oracle10g Database PL/SQL, from the optimizing compiler to nested table set operations. From there, it provides in-depth coverage of key new capabilities of PL/SQL from Oracle9i, including table functions, multi-level and string-indexed collections, and dynamic SQL method 4 with both Native Dynamic SQL and DBMS_SQL. After attending this seminar, you will have a strong understanding of how to best take advantage of Oracle PL/SQL to solve the programming challenges you face I the 21st Century.

Detailed Description

This seminar starts with a review of key new features in Oracle10g PL/SQL, from the optimizing compiler to automatic compiler warnings. From there we move on to in-depth coverage of advanced features of PL/SQL collections, including:

• Use of collections with FORALL, and the new INDICES OF and VALUES OF clauses available in Oracle10g.
• The BULK COLLECT mechanism for rapid retrieval of multiple rows, with an emphasis on employing the LIMIT clause.
• The SAVE EXCEPTIONS feature of FORALL, new to Oracle9i.
• Multi-level collections, including emulation of multi-dimensional arrays, nesting collections within objects and more.
• String-based indexing, which allows us to emulate a relational table's unique indexes for a collection, and also offers many new and interesting ways to store program data.
• The application of nested tables to table functions, and from there the interesting new capabilities inherent in PIPELINED functions.
• Oracle10g MULTISET operators for nested tables, allowing us to manipulate the contents of a nested table with set operations similar to those available in SQL.

With collections well and fully covered, we will move on to dynamic SQL. Again, assuming that you have a working familiarity with EXECUTE IMMEDIATE, we will dive into the following topics:

• Method 4 Dynamic SQL with both Native Dynamic SQL and DBMS_SQL. Method 4 is the most challenging kind of dynamic SQL to write.
• Parsing very long strings with DBMS_SQL. NDS is limited to SQL strings of no more than 32K characters. What do you do when you need more?

Having played around with one useful built-in package (DBMS_SQL), we will then (as time allows) spend some time on other commonly-used or highly useful packages, including:

• UTL_FILE: Oracle enhanced UTL_FILE in a number of crucial ways in Oracle9i Release 2. You can now work with database directories instead of UTL_FILE_DIR, remove files, copy and rename files, and get attribute information about files.
• DBMS_SCHEDULER: New to Oracle10g, this package replaces DBMS_JOB and offers much more robust database-level job scheduling.
• UTL_MAIL: A new package that offers improved ease of use over UTL_SMTP to send mail from within your PL/SQL code base.
• UTL_RECOMP: an upgrade to previous built-in Oracle functionality to recompile invalid program units.

Along the way, we will cover in context the following useful fundamentals of PL/SQL:

• Autonomous transactions: the ability to commit or rollback changes in a single PL/SQL block without affecting the outer or main transaction.
• Invoker rights: specifying that a store program runs under the authority of the current user or invoker, rather than the definer/owner of the program

As with any seminar taught by Steven Feuerstein, you will receive guidance on the best way to use all of these features, to ensure that your code is readable, easy to maintain and efficient.

Download Now! (520 KB, Updated: April 4, 2005)

This seminar introduces you to many of the latest and greatest capabilities in the Oracle PL/SQL language, with a focus on new features in Oracle9i and Oracle10g. After viewing this seminar, you will have a broader understanding of what is possible in the PL/SQL language and how best to write code using these features.

The Wonders of Oracle9i PL/SQL
The improvements revealed in the Oracle9i PL/SQL language demonstrate clearly that Oracle is committed to the PL/SQL language, in terms of both enhanced performance and feature robustness. We will explore some of the most important advances in Oracle9i PL/SQL, including:

• Support for inheritance in Oracle object types: You can now (finally) define "class hierarchies" with object types, which will make Oracle's object model much more attractive and useful.
   
• Table functions and cursor expressions: You can now call a function from within the FROM clause of a SQL query, greatly expanding the flexibility and in some cases performance of your application (most especially in data warehousing applications).
   
• Enhanced date and timestamp functionality: You can now define timestamps with fractional second precision to 9 digits. The new INTERVAL datatype allows you to store and manipulate with great accuracy the amount of time between two dates or timestamps.
   

Work with XML data inside PL/SQL
XML is rapidly becoming the de facto standard for the transmission of data between the many distributed components that are deployed via the Internet to solve pressing business needs. Oracle has moved aggressively to support XML inside the database. This seminar will introduce you to the basics of XML documents and show how to parse, manipulate and transform data between tables and XML documents.

Delight in the wonders of Native Dynamic SQL
In Oracle8i, Oracle introduced a new way of designing and executing dynamically (run-time) constructed SQL and PL/SQL statements: native dynamic SQL or NDS. With NDS, you will find dynamic SQL much easier and faster to write and execute. We will explore the new EXECUTE IMMEDIATE and OPEN FOR statements and entertain ourselves with all the magical things you can do with NDS.

Download Now! (1.1 MB, Updated: 11/22/04)

Aren't you tired of writing programs the same old way, knowing you should take more care with the code, feeling that they need to be tested more, experience "deja vu coding" -- writing something you know you or someone else must already have written? "This Old Program, These Old Programmers" aims to help you jettison the old way of writing your applications, and help you establish new and better patterns of programming. This seminar presents crucial guidelines for high quality development (including new ideas on error handling) and then focuses on three main topics:

• Guidelines for high quality development: key elements required to ensure that the code you right meets user requirements, works efficiently and is very maintainable.
  
• Doing it right the first time: Steven steps through the development of a utility, demonstrating best practices that help you keep your code readable, manageable and (relatively) free of bugs.
  
• Fixing it right the second time: No matter how disciplined we are, there is always room for improvement. This portion of the seminar introduces the concept of refactoring for PL/SQL developers. You will learn how to more rigorously apply best practices and sound programming techniques to existing code, improving its inner design, structure and overall robustness. 

Download Now! (Updated: 2/13/02)

[Note: this material is largely covered and superseded by the Brave New World seminar.]

This advanced techniques class is designed to help PL/SQL developers come up to speed as quickly as possible on the latest and greatest PL/SQL-related features of Oracle8i. It also covers crucial advanced techniques available in all versions of Oracle from Oracle 7.3 forward. It builds on Steven's earlier, popular Advanced Techniques with coverage of a number of exciting new features for PL/SQL developers in Oracle8i, including Native Dynamic SQL, Autonomous Transactions, the Invoker Rights Model, Calling Java from PL/SQL and Database and Schema Level Triggers. In addition to these Oracle8i topics, this seminar explores advanced techniques for package design and working with collections (index-by tables, nested tables and variable arrays).

Topics include...

• Package design: You may have heard of packages. You may even have built some yourself. This course takes you beyond the basics and explores techniques like the initialization section, overloading, and toggles and windows for improved flexibility and packaged cursors.
• Dynamic SQL: we will review the capabilities of DBMS_SQL, focusing on its more advanced usages. Then we move to native dynamic SQL, a new implementation of dynamic SQL available in Oracle8i that is faster and much easier to use than DBMS_SQL.
• Interoperability with Java and C: PL/SQL is no longer a closed language. You can, with a minimum of effort, now call C and Java programs from within your PL/SQL code. This section includes an introduction to the Java language.
• Collections: Oracle expanded upon PL/SQL tables to add nested tables and variable arrays. You now have more options than ever for implementing array-like structures; all you have to do is figure out how to use them properly!
• The invokers rights model: Oracle8i lets you define your stored programs so that they run under the authority of the invoker instead of the owner. This feature allows you to maintain a single set of code that works differently for different users. You can build shared dynamic SQL utilities that automatically apply to the calling schema, rather than the owner schema.
• Autonomous transactions: a long-awaited capability, Oracle8i now allows developers to specify that any DML in a specific PL/SQL block will be committed or rolled back without affecting the "main" transaction. What can you do with this feature? Write errors to a log and commit those entries without affecting the business transaction. Call functions from within SQL that update the database. Perform a commit inside a trigger. And more...
• Advanced Queuing: Oracle8 introduced a new and very exciting feature: Oracle Advanced Queuing. Oracle AQ implements a robust and flexible messaging architecture for Oracle-based applications. Access to Oracle AQ from within PL/SQL occurs through calls to the DBMS_AQ and DBMS_AQADM packages.
• Row-Level Security: The DBMS_RLS package, along with other new 8i features, implements row-level security or "fine grained access control" and offers exciting new possibilities for both developers and DBAs. You no longer have to build complex architectures based on views. Put all your restrictions in PL/SQL functions and let Oracle do all the work.
• Large Objects: Use the DBMS_LOB package to manipulate the new Large Objects available in Oracle since Version 8.0. Say goodbye to LONGs!