Check Out the Video Categories (click on a topic to expand)
Collections (array-like structures in PL/SQL) are critical data structures that are used in some of the most important features of PL/SQL. Every PL/SQL developer should have a deep familiarity with collections and the wide range of features that Oracle has implemented for them over the years. This series introduces collections and quickly moves on to detailed explanations of collection methods, how to leverage string indexing in associative arrays, multi-level collections, set-level operations on nested tables and more.
Programming with Collections: Introduction and Overview (18 minutes)
Defining Collection Types (14 minutes)
Working with Collection Methods (33 minutes)
Working with Associative Arrays (35 minutes)
Working with Nested Tables (24 minutes)
Working with Varrays (29 minutes)
Working with Collections inside SQL (20 minutes)
Non-Sequential Indexing in Associative Arrays (24 minutes)
Working with String-indexed Associative Arrays (35 minutes)
Working with Multilevel (Nested) Collections (51 minutes)
Set-Level Manipulation of Nested Tables (MULTISET) (24 minutes)
Consistent, robust error management is a critical feature of any successful application. Developers need to know all that is possible in PL/SQL regarding the raising, handling and logging of errors, and that standardize the way in which those tasks are performed. These videos take you beyond the basics of exception handling in PL/SQL to explore the wide range of specialized error management features in Oracle. We will cover FORALL's SAVE EXCEPTIONS, DML error logging with the DBMS_ERRLOG package, the AFTERSERVERERROR trigger, the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, and more.
Error Management Overview (6 minutes)
Basic Concepts of Error Management in PL/SQL (17 minutes)
Defining Exceptions in PL/SQL Blocks (27 minutes)
Raising Exceptions in PL/SQL Blocks (32 minutes)
Handling Exceptions Raised in a PL/SQL Block (42 minutes)
Continuing Past Exceptions: Overview and Nested Blocks (8 minutes)
Continuing Past Exceptions: FORALL and SAVE EXCEPTIONS (21 minutes)
Continuing Past Exceptions: DBMS_ERRLOG and LOG ERRORS (26 minutes)
Best Practices for Error Management 1 (35 minutes)
Best Practices for Error Management 2 (32 minutes)
The focus of this presentation is on enhancing the performance of multi-row SQL statements executed from within PL/SQL, using FORALL and BULK COLLECT. After attending this seminar, you'll be able to create new programs using the features discussed and use them to redesign existing programs safely for enhanced PL/SQL execution.
Turbo-charge PL/SQL Execution with Bulk Processing: Introduction and Overview (16 minutes)
Bulk Processing: Get Data Faster with BULK COLLECT (25 minutes)
Bulk Processing: Change Data Faster with FORALL (34 minutes)
Bulk Processing: How to Convert from "Old Fashioned" Code to Bulk (Fast) Implementations (27 minutes)
Dynamic SQL (construction, parsing and execution of SQL statements and PL/SQL blocks at runtime) has become a common and critical element of most modern applications. PL/SQL offers two distinct methods for dynamic SQL: native dynamic SQL (NDS) and DBMS_SQL. Videos in this section focuses primarily on the capabilities of native dynamic SQL: how to use EXECUTE IMMEDIATE to execute dynamic query, DML, and DDL statements. We will also go beyond the basics and delve into when to use DBMS_SQL (method 4 dynamic SQL), and new Oracle Database 11g features for dynamic SQL (most importantly, interoperability between DBMS_SQL and NDS.
Dynamic SQL in Oracle PL/SQL: Introduction and Overview (17 minutes)
Executing DDL and "Simple" DML Statements (Method 1 dynamic SQL) (24 minutes)
Executing Dynamic DML with Fixed Number of Bind Variables (Method 2) (28 minutes)
Executing Dynamic Queries with Fixed Number of Elements in SELECT List (Method 3 Dynamic SQL) (45 minutes)
Executing Dynamic PL/SQL Blocks (34 minutes)
Method 4 Dynamic SQL (28 minutes)
Advanced Topics in Dynamic SQL (15 minutes)
Best Practices for Writing Dynamic SQL (36 minutes)
Oracle11g Enhancements for Dynamic SQL (26 minutes)
This session takes an in-depth look at I/O (input/output). Even though most activity in PL/SQL programs involves interaction with the Oracle database, programs often find it necessary to perform I/O operations on files, display output on the screen (system output) and send e-mail. You will receive detailed information on how to use the UTL_FILE package to read and write files. The session then expands beyond the UTL_File package to explore the use of Java Stored Procedures and external tables to interact with files. From there, instruction on both the basics and advanced topics for the DBMS_OUTPUT package (screen output) is presented. The session wraps up with an overview of UTL_MAIL’s e-mail sending capabilities.
Introduction to Input/Output in PL/SQL (5 minutes)
Introduction to the UTL_FILE Package (18 minutes)
Reading and Writing Files with UTL_FILE (20 minutes)
High-Level Features of the UTL_FILE Package (15 minutes)
Using DBMS_LOB to Work with BFILEs (18 minutes)
Using External Tables to Read Files (12 minutes)
Using DBMS_OUTPUT to Send Show Output on Screen (19 minutes)
Sending Email from within PL/SQL (6 minutes)
Software managers (and, therefore, their developers) are very focused on delivering applications "on time" (that is, to meet the current production deadline). Sure, that's important. But as (or maybe even more) important is to build applications so that they can be maintained and enhanced without consuming all the resources of the development team (thereby interfering with new development). This webinar offers a wide-ranging set of techniques to make your code easy to understand and maintain over time. These techniques include: the use of subtypes and local modules; how to activate and check compliance with standards; and encapsulation (information hiding).
Introduction and Regression Testing (15 minutes)
Defining, Following and Verifying Standards (33 minutes)
Leveraging Readability Features of Oracle PL/SQL (39 minutes)
Write Modular Code with Packages (51 minutes)
Write Tiny Chunks of Code (32 minutes)
Everyone knows that hard-coding is a bad idea. Too many developers, however, only think in terms of literal values when they think of hard-coding. There are, unfortunately, many ways that hard-coding manifests itself in our programs. This webinar offers a comprehensive look at all the types of hard-coding that can appear in your programs, from literals to explicit declarations to exposed formulas, and offers specific techniques to get rid of the hard-coding. The result is code that is much easier to read and to maintain.
Say Goodbye to Hard-Coding: Introduction and Concepts (22 minutes)
Avoid Hard-Coding of Magic Values (21 minutes)
Hiding Business Rules and Formulas (18 minutes)
Avoid Hard-Coding in Declarations (30 minutes)
Stop Writing So Much SQL! (37 minutes)
Oracle PL/SQL makes writing SQL incredibly easy. In fact, it is way too easy to write SQL. As a result, PL/SQL developers take SQL totally for granted and very few organizations have any sort of guidelines in place for when, where and how to write SQL. The result is that the same or similar SQL statements are repeated throughout the application, making it very hard to optimize and maintain code. This session starts by reviewing the role of SQL in PL/SQL applications, and the key challenges involved when writing SQL. The presentation will then step through the most important best practices for SQL construction, ranging from avoiding SQL repetition to full qualification of variable and column names.
Introduction and Overview (4 minutes)
When and Where to Write SQL in PL/SQL (21 minutes)
Leveraging Key PL/SQL Features for Writing SQL (24 minutes)
Writing code is just one (albeit very important) aspect of building applications and fully leveraging PL/SQL. As your code volume increases and as Oracle offers more and more options for configuring PL/SQL, it becomes more important to take full advantage of advanced PL/SQL compiler features, data dictionary views and so much more. These videos show developers how to correctly apply the PL/SQL compiler optimizer, compile-time warnings and conditional compilation. We then review and learn how to "mine" the most important data dictionary views for PL/SQL developers, from such all-time favorites as ALL_SOURCE to the new PL/Scope ALL_IDENTIFERS and on to ALL_PLSQL_OBJECT_SETTIJNGS.
Introduction to Effective Management of PL/SQL Applications (11 minutes)
The Optimizing Compiler of Oracle PL/SQL (30 minutes)
Conditional Compilation (31 minutes)
Analyzing PL/SQL Code Using Data Dictionary Views (27 minutes)
Using PL/Scope to Analyze PL/SQL Code (31 minutes)
Runtime Memory Architecture of PL/SQL (28 minutes)
Managing Dependencies and Invalidations (24 minutes)