ODTUG OPP Home

Check out the proposed classes at OPP2009!
Here are the categories for presentations.

Applied PL/SQL	These sessions show how to use apply PL/SQL to solve a variety of problems and meet challenges.
Best Practices	Designed to help you write code that has fewer bugs and is easier to understand and maintain.
Deep Dive	Going beyond the fundamentals, a Deep Dive session covers advanced aspects of core PL/SQL features.
Fundamentals	Covers fundamental features of PL/SQL, intended primarily for those developers who are relatively new to the PL/SQL language.
Performance	Techniques for improving the performance of your PL/SQL-based applications
Tools	Tools for working with PL/SQL

Applied PL/SQL
These sessions show how to use apply PL/SQL to solve a variety of problems and meet challenges.

PL/SQL Internet Programming
John Jeunnette, Prairie Systems Group
PL/SQL programming has always been about getting the most out of the data, but now it is all about the Web. Are you being challenged to develop or convert your applications to a web-based environment? If not, chances are you will be soon.

The Oracle database includes packages to generate HTML tagging and the
"mod_plsql" for the Oracle HTTP Server (OHS) to connect HTTP requests to database objects and vice versa. The result is an efficient and easy to program (for PL/SQL programmers, anyway) web-based application development environment that takes maximum advantage of the database.

This session will cover an overview of the HTML web-based application development technology and specific nuances included in the Oracle PL/SQL Web Toolkit. We will examine the technology, backend, and user interface of a "typical" application. Coding suggestions, tips, and examples of "what not to do" will be included.

Back to top

Stateless Application Issues
John Jeunnette, Prairie Systems Group

A major feature of the HTTP protocol is the "response/request" nature of the client to server interaction. Because of this "stateless" operation an application will not have a persistent connection to the web server or, by extension, a database. Some application designs are not appropriate for this environment or require major middle tier layers (EJB 3.0 comes to mind). But what if we can keep the simple "block mode" communication of HTTP and still provide stable and secure applications?

This presentation will review a number of issues and suggested solutions for stateless web-based applications (specifically, with the PL/SQL Web Toolkit but applicable to any web application technology).
(Stateless in the sense that every "page" or, in this case, procedure call is independent of any other procedure.)

Specific issues include

    * session identification
    * database concurrency control
    * multiple HTML form objects (check boxes, select lists, radio buttons)
    * browser timeouts during long running processes
    * "back" button navigation

Examples of the issues and solutions will be reviewed and demonstrated.

Back to top

Advanced PL/SQL Web Toolkit Topics
John Jeunnette, Prairie Systems Group

What if you need the database to communicate directly to a web service or other API (Application Programming Interface) using the HTTP protocol? Use the UTL_HTTP system utility package! A common example in an e-commerce environment is credit card authorization. Credit card authorization services architectures range from an application where all credit card data collection and authorization processing happens on the authorization service servers to a request/response API where you collect all data locally and pass some of it to the authorization service and receive authorization code and/or error code responses.

The first part of this session will cover the use of the UTL_HTTP package to communicate with another web-based application or web service. Details of the request/response processing will be reviewed in a credit card authorization routine and a procedure to evaluate the response time of a web page.

The second part of this session will look at the image/document storage options of the PL/SQL Web Toolkit. Client files of any type may be uploaded to the database (BLOB or LONG RAW data types). An example of a "simple" source code control and document management applications will be demonstrated plus an application where all graphic images are stored and served from the database.

Back to top

APEX for PL/SQL Developers
Scott Spendolini, Sumner Technologies
You’ve heard all of the buzz, but just haven’t had the time to check out just what APEX is and does. Well, make no more excuses! This session will provide a brief overview of what APEX is, how it works, and how you can leverage your PL/SQL skills to start building web applications in no time at all. It will then highlight the specific areas where you as a PL/SQL developer can expand the capabilities of the tool. Ample time will be given to a questions and answer session.

Back to top

Introduction to Oracle and XML
John King, King Training Resources
Introduces Oracle's XML support including basics of XML, Oracle XML DB, XMLType, XML Functions, and Storage options. Topics include:

XML Overview
XML DB Concepts and Features
Native Oracle XML Functions & SQL/XML Functions
XML DB Repository Introduction
XMLType Datatype, Methods, and Storage options
Loading data into XMLType

Explores the various options for storing XMLType data and how to choose the appropriate mechanism for your needs. Attendees will also learn about using
XML DB functions to retrieve and modify XMLType data and indexing options for XML data. Topics include:

Choosing XMLType (Unstructured, Structured, Binary)
SQL constraints and XML data
Using XML Schema and DBMS_XMLSCHEMA
Retrieving XMLType data
Using XPath functions on XMLType data
Executing SQL operations on XMLType
Performing DML on XML
DeleteXML() and existsNode()

Discusses PL/SQL interaction with XML including PL/SQL XML APIs such as DBMS_XMLDOM, DBMS_XMLPARSER, and more. Topics include:

How PL/SQL and XML Interact
DBMS_XMLSTORE
DBMS_XMLGEN
DBMS_XMLQUERY and XQuery within PL/SQL
PL/SQL APIs for XMLType, including Document Object Model(DOM) Overview; DBMS_XMLDOM (DOM documents); DBMS_XMLPARSER (access XML documents); Using DBMS_XSLPROCESSOR (transform XML documents)

Illustrates performance issues related to XML And XML use. Techniques to reduce the performance impact of XML will be discussed. Topics include:

XML Performance Issues
Use of indexes
XML Storage types

Back to top

Implement Search with PL/SQL and Oracle Text
Ron Hardman, Academy OnDemand
Unstructured data: Documents or large blocks of text that are stored in the database, on a file server, or are available via URL. They may be completely unorganized, or loosely organized via some type of categorization, and may appear in many forms. XML, MS Word, HTML, PDF, and PowerPoint are common formats, but there may be others. Typical indexes do not work. Standard query techniques do not work. What is a PL/SQL developer to do?

According to multiple Gartner studies, unstructured data in an organization doubles every three months, and approximately 80 percent of business is conducted based on data that is stored in an unstructured way. According to Butler, 85 percent of data in most organizations is unstructured.

You have unstructured data in your organization. Lots of it. In this session I will show you how to access it in your PL/SQL-based applications using Oracle Text. Oracle Text is the full-text indexing and search feature that is built into every edition of the Oracle database, including Express Edition. We will cover:

* When is using a Text index appropriate.
* Types of features that are available to you, including differences by version.
* Filtering different documents.
* Index structure.
* Query structure.
* Working with multilingual documents.
* Any question, from beginning to advanced, that you have on the topic.

Back to top

Best Practices
Designed to help you write code that has fewer bugs and is easier to understand and maintain.

Writing Maintainable Code
Don Bales, Independant Consultant
Building modular, reusable code doesn't happen by chance. We have to learn how to "just say no"…to spaghetti code that is impossible to read, maintain, or enhance. In this presentation, Don will show you how to take advantage of packages, local subprogram units, cursor variables, table functions, autonomous transactions, and more to craft small, reusable units of code that are easy to maintain—and to test.

Back to top

Automating Testing for PL/SQL
Steven Feuerstein, Quest Software
We all know we should test our code more thoroughly, but who has the time and patience, and isn't our code "good enough" anyway? Did you know that it is widely accepted that if you have a program of five hundred lines, you should expect to write five thousand lines of test code? Ah, so that's why we don't test our code! If you are not satisfied with the number of bugs in your code or if you sometimes find yourself embarrassed demonstrating your software to users, then attend this session. You will learn how you can automate the testing process, reduce bugs in your code, and increase confidence in your applications. This session reviews the tools, from open source to commercial, that offer varying levels of testing automation.
Back to top

How to Write SQL Injection-Proof PL/SQL
Bryn Llewellyn, Oracle Corporation
Googling “SQL injection” gets about 3 million hits. The topic excites interest and superstitious fear. This presentation demystifies the topic and explains a straightforward approach to writing database PL/SQL programs that provably guarantees their immunity to SQL injection.

Of course, one cannot avoid what one cannot define ¬ and so we start with a definition of SQL injection. This, in turn, leads to the understanding that it is possible only when a PL/SQL subprogram executes a SQL statement whose text it has created at run time. Clearly, then, the best way to avoid SQL injection is to execute only SQL statements whose text is fixed when the PL/SQL that executes it is compiled.

Much of the presentation deals, therefore, with requirements scenarios that seem to require the run-time creation of SQL statements. However, such requirements scenarios are very much fewer than many programmers think; many can be implemented satisfactorily using compile-time-fixed SQL statement text. Several such scenarios are illustrated.

The rest of the presentation deals with meeting the relatively rare scenarios that non-negotiably require run-time-created SQL statement text. It explains, and insists on, rules which guarantee proof against injection in this regime. These rules are surprisingly easy to follow; and, as a bonus, they insure semantic correctness in edge cases that programmers often overlook.

Back to top

Doing SQL in PL/SQL: Best and Worst Practices
Bryn Llewellyn, Oracle Corporation
The PL/SQL developer has many constructs for executing SQL statements, and the space of possibilities has several dimensions: embedded SQL versus native dynamic SQL versus the DBMS_Sql API; bulk versus non-bulk; implicit cursor versus parameterized explicit cursor versus ref cursor; and so on. Deciding which to use might seem daunting. Moreover, as new variants have been introduced, older ones sometimes have ceased to be the optimal choice. Oracle Database 11g has kept up the tradition by bringing some improvements in the area of dynamic SQL.

This session examines and categorizes the use cases for doing SQL from PL/SQL, takes the Oracle Database 11g viewpoint, and explains the optimal approach for the task at hand.

Back to top

Error Management for PL/SQL Applications
Steven Feuerstein, Quest Software
This session takes you beyond the basics of exception handling in PL/SQL to explore the wide range of specialized error management features in Oracle, plus recommendations for best practices for consistent, robust application construction. We will talk about FORALL's SAVE EXCEPTIONS, the DBMS_ERRLOG package, the AFTERSERVERERROR trigger, the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, and more. In addition, we will talk about hiding error management functionality behind a PL/SQL packaged API and then deploying it as a set of services for application developers.

Back to top

Deep Dive
Going beyond the fundamentals, a Deep Dive session covers advanced aspects of core PL/SQL features.

Object-Oriented Development in PL/SQL
Don Bales, Computer applications consultant
While an object model has been available in Oracle and PL/SQL since Oracle 8, it was only in Oracle9i Release 2 and the support for inheritance that the Oracle object type has finally become a viable alternative (or complement) to standard relational design. This session will give you a basic, working knowledge of object types and when you would find them useful.

Back to top

Advanced Capabilities with Collections
Steven Feuerstein, Quest Software
If you know the basics of collections, then you're just beginning to scratch the surface of what is possible. This session broadens your awareness by covering such topics as string indexing of collections, multi-level or nested collections, MUTLISET operators and table functions.
Back to top

Conditional Compilation
Bryn Llewellyn, Oracle Corporation
Oracle Database 10g Release 2 introduces support for a powerful new PL/SQL language feature: conditional compilation. The feature is elegant and easy to understand and has many interesting uses. Some of these may not have occurred to you. This session will illustrate a set of use cases with code samples and by doing so will demonstrate every conditional compilation construct. It will recommend best practices by discussing alternative implementation approaches to the use cases. The session will also briefly review the other new PL/SQL features brought by Oracle Database 10g Release 2: relaxation of line length and total output volume in the Dbms_Output package; the Dbms_Ddl.Create_Wrapped() procedure to allow PL/SQL programs created by a PL/SQL program to be wrapped; the Utl_Nla package exposing an implementation of the popular BLAS and LAPACK libraries for matrix math.

Back to top

Globalization/Localization in PL/SQL Applications
Ron Hardman, Academy OnDemand
Unicode, character sets, National Language Support (NLS), character semantics, code points, glyphs—what does it all mean? These are all concepts related to Oracle's globalization support, and are important to you if your application needs to support non-western languages, multiple languages, currencies, and time zones.
This session covers globalization and localization in Oracle—specifically how the business strategies of globalization are translated to PL/SQL-based programs. The following questions are addressed:

How is globalization different from localization
What is Unicode?
What should be considered when choosing a database character set?
When should globalization strategies be considered in your application design?
How does character semantics impact PL/SQL programming and your data model?
What string functions are available for global applications?
How are time zones handled?
What support is available for multiple currencies?
What search capabilities are available for multi-lingual data?

This topic can literally open your applications up to the world. We'll run examples, discuss projects, and break this complex topic into components that you can use immediately.

Advanced Testing Topics
Finn Ellebaek Nielsen, Ellebaek Consulting ApS
This session introduces best practices for tackling some of the difficult impediments related to automated testing of Oracle code. This includes areas such as test data setup, dependencies, code coverage, and automated generation of test cases.

An intermediate knowledge of PL/SQL is assumed. Preliminary knowledge of automated testing of PL/SQL is a benefit, e.g. through the session, "Automating Testing Options for PL/SQL.”

Back to top

Fundamentals
Covers fundamental features of PL/SQL, intended primarily for those developers who are relatively new to the PL/SQL language.

Introduction to Collections and Records
Mary Gable, Preferred Training Solutions
Without a familiarity with collections (array-like structures in PL/SQL) and records (the PL/SQL analogue of a table's row), it is very difficult to write high quality, efficient PL/SQL programs. This session will ensure you know how to declare, populate and manipulate all three types of collections: associative arrays (PL/SQL tables), nested tables, and varrays, and when to use each different type of collection.

Back to top

Introduction to PL/SQL Exceptions
Mary Gable, Preferred Training Solutions
This presentation will focus on error handling and the use of nested blocks for exception handling and propagation. We will discuss scope and visibility of PL/SQL objects, all three types of exceptions, and how to code and propagate exception handlers with nested blocks. We will also see examples of function calls using the DBMS_UTILITY package used to display detailed error information – a couple of which were new with version 10g.

Back to top

Writing SQL in PL/SQL
Warren Capps Illuminations, Inc.
A very rapid overview of the SQL language, followed by a primer on who to write SQL statements inside PL/SQL blocks. Topics include explicit and implicit cursors, cursor attributes, the cursor FOR loop and common errors.

Modular Construction in PL/SQL
Mary Gable, Preferred Training Solutions
The only way to manage the complexity of a large application is to break up the logic into lots of relatively small, reusable, named program units. In PL/SQL, you can and should build your application based on packages, procedures, and functions (and maybe even object types, but those are discussed elsewhere). This session introduces developers to the basics of block structure in PL/SQL and how to build named program units.

Back to top

Introduction to Dynamic SQL
Warren Capps, Illuminations, Inc.
Explores the capabilities of native dynamic SQL: how to use EXECUTE IMMEDIATE to execute dynamic query, DML, and DDL statements.

Back to top

Performance
Techniques for improving the performance of your PL/SQL-based applications

Performance Instrumentation
Karen Morton, Agilex
Performance instrumentation is a little extra code that developers put into their PL/SQL applications that tells everyone—owners, users, system managers, and the software developers themselves—exactly where your software is spending your time. With it, managing performance is a snap. Good instrumentation makes it so normal people, not just specialists, can diagnose and solve performance problems quickly and permanently, often before your users even sense something is wrong. However, without good time-based performance instrumentation, managing performance becomes nightmarishly complex and expensive.

With as little as two lines of code you will be able to monitor specific tasks in your application and make one of the most difficult steps of implementing response-time based problem diagnosis (Method R) into one of the easiest. In addition, these techniques will enable you to correlate database statistics back to business tasks and help discover those tasks that are heavy resource consumers. This presentation will provide examples of how to perform the instrumentation using Oracle's built-in packages and a free open source instrumentation package called the Instrumentation Library for Oracle (ILO). Its cost on every level is free, and the significant collateral benefits that can be achieved will make code instrumentation part of your coding standards.

Back to top

Optimizing SQL with Collections
Patrick Barel, AMIS
Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: FORALL and BULK COLLECT. This session demonstrates the power of these features and offers in-depth guidance on how to apply them.

Back to top

Pipelined Table Functions
Patrick Barel, AMIS
Pipelined table functions offer an ideal convergence of the elegance and simplicity of PL/SQL with the performance of SQL. Complex data transformations are effortless to develop and support with PL/SQL, yet to achieve high-performance data processing, we often resort to set-based SQL solutions. Pipelined functions bridge the gap between the two methods effortlessly, but they also have some unique performance features of their own, making them a superb performance optimization tool.

Back to top

Writing Modular Code
Warren Capps, Illuminations

This presentation discusses the types of stored PL/SQL code and all of the additional considerations necessary for stored code in the database such as privileges, dependencies, deployment, and source control. Use of stored procedures, functions, packages, and triggers will be demonstrated during an overview of PL/SQL’s modular construction capabilities.

Back to top

Tools

Writing PL/SQL Productivity Gains with Oracle SQL Developer
Kris Rice, Oracle Corporation
If you spend much of your day writing PL/SQL having a few pointers on being more productive while you're at it, should be useful. SQL Developer is a GUI client that accesses the database. You can use the tool to write PL/SQL much as you do in a command line tool. Unlike a command line, SQL Developer offers code insight and completion, it has code snippets and you can create and use code templates. It builds package body skeletons based on the detail in the package specification and helps you run, debug and even remote debug your code. In this session we look at the PL/SQL Hierarchical profiler, the search mechanism that takes advantage of PLScope, refactoring and other code assistants provided in the tool to assist with PL/SQL coding.

Back to top

Toad Hidden Treasures for Developers
Ben Boise, Quest Software
As a developer or DBA, you may already be using Toad to build and maintain programs, debug, tune code, or automate administration tasks. But did you know you can use Toad to do even more? It's true!
• Manage development projects and share project assets such as templates, scripts, and code snippets
• Record workflow steps for playback or schedule them to run at a later date
• Extract objects and data from an Oracle export file, instead of having to import everything
• Perform code quality reviews and easily find problematic code
• Automate the SQL optimization process

Back to top

Toad Tips and Tricks for DBAs
Ben Boise, Quest Software
Toad users, come and see how to maximize, both developer and DBA, productivity using powerful shortcuts, overlooked features, and advanced capabilities. This session will have you saying “Wow - I didn't know Toad could do that--and so easily!” You'll learn easy, yet extraordinary, ways to make both new and veteran Toad users more productive, and thus increase the value of Toad usage within your company. Our goal will be to show you how to get the most out of your Toad experience. And this session is being brought to you by people who know Toad best--the product manager and product architect. There might even be time for some product future roadmap discussion and upcoming feature previews!

Back to top

PL/SQL Developer: Tips and Tricks
Patrick Barel, AMIS
Of course you can use a hammer to tighten a screw, but that doesn't mean it is the best tool for the job. Or even for driving a nail in a board, there are a number of hammers to choose from. The same is true for PL/SQL development. There are a lot of similar tools in the market today, but you want to use the best one. The best tool for PL/SQL development is PL/SQL Developer. This tool helps to get the job done, fast. Everything you need is there, and in case you might miss something, you can build your own plug-in. PL/SQL Developer is your extensible toolbox. It’s your "one-size-fits-all-hammer."

Back to top

Data Modeling with Oracle SQL Developer Data Modeler
Kris Rice, Oracle Corporation
Oracle SQL Developer Data Modeler supports logical and physical data modeling for Oracle, Microsoft SQL Server and IBM DB2. This addition to the Oracle SQL Developer family of tools provides forward and reverse engineering of database structures for all who work with graphical data models. In this session, see how to create a logical entity relationship diagram, with a choice of Barker or Bachman notations, and forward engineer the design to one or more relational schema diagrams. The session reviews various diagramming options and the set of Design Rules provided to help ensure your models comply with a set of standards. You hear about the implementation specific physical models and review the DDL generated for the models designed.

Back to top

Oracle SQL Developer: User Extensions, Tuning and Other Interesting Features
Kris Rice, Oracle Corporation
Oracle SQL Developer supports a wide range of features for everyday use. Some features are less frequently used; they’re only needed once, such as Migrations, they may be created and shared, such as User Extensions, or they’re less well known, such as PL/SQL Debugging or Source Code Control. In this session you hear how these often-lesser known or used features can improve productivity. Initially reviewing user defined extensions; we’ll look at how you can use SQL Developer to refactor and even tune SQL code, using reports. We’ll look at how you can use the integrated file navigator and version control and, for Application Express users, we show a number of useful integration points between the two products.

Back to top