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 21^st 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.