Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Teaching Oracle PL/SQL class

Re: Teaching Oracle PL/SQL class

From: DA Morgan <>
Date: Wed, 17 Oct 2007 09:10:39 -0700
Message-ID: <> wrote:

> On Oct 17, 1:38 am, DA Morgan <> wrote:

>> Comments in no particular order.
>> 1. Why are you teaching CONSTANTS with PLS_INTEGER rather than with
> good point

>> 2. Why is "Anchored Types" in there multiple times.
> mistake

>> I could go on but to me what you have presented is disjointed and
>> doesn't flow logically. Try working from this:
>> Class 1: Concepts and architecture of the PL/SQL programming language. A
>> review of the programmable object types and the data dictionary objects
>> that support them.
>> Class 2: The basis of all PL/SQL programming is anonymous blocks. The
>> session begins with the basic structure of the anonymous block and then
>> extends it into writing functions and pipelined table functions.
>> Students learn the data types available for PL/SQL programming and how
>> to declare variables and constants.
>> Class 3: This session focuses on turning the basic anonymous block into
>> a stored procedure and how to manage IN, OUT, IN OUT, and DEFAULT
>> parameter declarations. Also demonstrates the use of NOCOPY.
>> Class 4: This session continues the discussion of stored procedures
>> focusing on control structures, cursors, and array processing. The
>> second half of the session focuses on using Packages to create libraries
>> of functions and procedures. As well as on Package distinct capabilities
>> such as the initialization section and serially reusability.
>> Class 5: This session focuses on the many types of triggers available
>> including table, instead-of, DDL event, and system event triggers.
>> Class 6: Not all queries and SQL statements can be written in advance
>> and all relational database products have the capability of dynamic
>> creating statements on demand. This session focuses on the three ways of
>> doing this: The DBMS_SQL built-in package, Native Dynamic SQL, and REF
>> Class 7: The previous sessions in this quarter have demonstrated basic
>> exception handling skills. In this session we explore, in great depth,
>> the skills for creating, managing, logging, and handling exception
>> created by Oracle and also application specific exceptions created by
>> developers including Named Exceptions, pragma Exception_init, RAISE,
>> Class 8: A pragmas is a compiler directive that modifies the default
>> behavior of a PL/SQL object. In this section we explore the use of the
>> autonomous transaction and inline pragmas. The second half of the class
>> builds on previous experience creating functions to create operators.
>> Class 9: Database applications, to be usable, must be stable, scalable,
>> and perform to meet the requirements of a service level agreement. This
>> session focuses on the skills, techniques, and tools used to identify
>> poorly performing PL/SQL and the skills for its optimization. The
>> built-in DBMS_PROFILER and DBMS_HPROF package, and their reports are
>> explored.
>> Class 10: To effectively work with PL/SQL programming requires a
>> knowledge of tools for source code encryption and that provide APIs to
>> built-in capabilities for many internal and external capabilities. This
>> session focuses on the WRAP utility and a number of commonly used
>> built-in packages.
>> Juggle it to fit into your dates and times.
>> --
> Good points.  Thanks for your input. I will reorganize with a more
> functional or task based organization.  It's a struggle trying to do
> it in 9 classes though.

What I posted is based on 10 x 3hr. classes: 30 hours.

Hope this helps.

Daniel A. Morgan
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Wed Oct 17 2007 - 11:10:39 CDT

Original text of this message