Re: Teaching Oracle PL/SQL class

www.douglassdavis.com wrote:
> Hello,
>
> I am teaching an Oracle PL/SQL class. The class will be 9 nights, < 3
> hours each night.
>
> Outline that I have so far for class is here:
>
> http://plsqltopics.wikispaces.com/
>
> I am in search of the most important topics to cover based on how the
> PL/SQL is used in industry. I also am in search of tasks that can be
> done in a lab type setting that would be close to those done in
> industry but not take more than 30 minutes each. If you have any
> comments, you may add to discussion section. If there are any topics
> you deem important that have been missed, you may add them directly to
> the wiki (note: you may have to go into text mode to edit outline).
>
> Thank you.
>
>
> Here is what I have so far (for new updates please go to
> http://plsqltopics.wikispaces.com/ ):
>
> Nine Day Class outline
>
>
> 1.
> Day 1 - SQL, SQL*Plus, and PL/SQL overview (ch 1,2,3, start 4)
> 1. Topics
> 1. SQL Review
> 2. About PL/SQL
> 3. Sql*Plus
> 4. console input/output
> 5. anonymous blocks
> 6. variables
> 7. Anchored Types
> 8. SELECT
> 2. Why SQL*Plus? Common Uses?
> 1. connect to DB
> 2. utilities: logging/running scripts/substitution vars
> 3. Why PL/SQL? Common Uses?
> 1. used for procedural tasks
> 2. Tight Integration with SQL / Higher Productivity
> 3. Runs on any Oracle DB
> 4. Tight Security/ App code on server/ can be
> obfuscated
> 5. Access to Pre-defined Packages
> 6. Support for Object-Oriented Programming
> 7. Support for Developing Web Applications and Pages
> 8. efficient/less network traffic than doing on client
> 9. can be stored on server or client
> 4. Subtopics
> 1. Theory: set oriented/4th Gen vs. procedural/3rd Gen
> 2. How PL/SQL works w/ server
> 3. Sql*Plus: screen size, logging, running scripts,
> substitution vars
> 4. PL/SQL block structure
> 5. server output
> 6. retrieving errors and warnings
> 7. variables
> 8. naming conventions
> 9. Anchored types / single point of definition
> 10. SELECT INTO. errors w/too many/too few rows
> 2.
> Day 2 - More SQL and Selection/Decision ( ch4 (continued), 5,6)
> 1. Topics
> 1. Continue SELECT, Other SQL in PL/SQL
> 2. IF Statements
> 3. Case
> 2. Why If/Case? Common Uses?
> 1. Selection
> 3. Subtopics
> 1. types of SQL that can go in PL/SQL
> 2. FOR UPDATE [OF] and Oracle Concurrency
> 3. logical operators
> 4. comparing nulls
> 5. ELSIFS - mk mutually exclusive
> 6. PL_INTEGER
> 7. CONSTANTS
> 8. nested blocks (and use w/if to save resources)
> 3.
> Day 3 - Procedures, Functions, Start Exception Handling (ch 7,
> 12, 13)
> 1. Topics
> 1. i. Start Exception Handling
> 2. ii. Procedures and Functions
> 2. Why Procedures/Functions? Common Uses?
> 1. Store Code for later use
> 2. Hide SQL/ provide interface to SQL
> 3. Utilities to be run at certain times
> 3. Why Exception Handling? Common Uses?
> 1. uniform way to represent errors
> 2. Catch/handle errors
> 3. Catch errors/re-report
> 4. Subtopics
> 1. named notation for actual params (arguments)
> 2. getting stored code info
> 3. scope
> 4. NOCOPY
> 5. params and in/out/in out
> 6. tiggers for more than one DML: INSERT/UPDATE
> 4.
> Day 4 - Start Loops and Cursors(ch 8,9)
> 1. Main Topics:
> 1. Loops: Simple/While/For
> 2. Start Cursors
> 3. Parameterized Cursors
> 2. Why loops? Common Uses?
> 1. repetition
> 2. process items w/cursors
> 3. process table (array) data struct
> 3. Why cursors? Common Uses?
> 1. process SQL statement
> 2. process lists of data
> 4. Subtopics
> 1. when to use each type of loop
> 2. ORA-01555 snapshot too old
> 3. nested loops
> 4. record types
> 5. cursor attributes
> 5. Day 5 - Advanced Cursors and Bulk SQL (ch 15)
> 1. Topics
> 1. Advanced Cursors:
> 1. parameterized
> 2. Ref Cursors
> 2. Bulk SQL
> 1. FORALL
> 2. BULK COLLECT
> 2. Why Bulk SQL Used? Common Uses?
> 1. FORALL - issue many queries at once
> 2. BULK COLLECT - retrieve results at once instead of
> one row at a time.
>
> more efficient
>
> 1. Why Ref Cursors Used? Common Uses?
> 1. pointer to cursor
> 2. can pass reference to cursor to different program
> units.
> 2. Subtopics
> 1.
> Day 6 - Triggers (ch 17)
> 1. Triggers
> 2. Why Triggers? Common Uses?
> 1. Automatically generate derived column values
> 2. Prevent invalid transactions
> 3. Enforce complex security authorizations
> 4. Enforce referential integrity across nodes in a
> distributed database
> 5. Enforce complex business rules
> 6. Provide transparent event logging
> 7. Gather statistics on table access
> 8. Modify table data when DML statements are issued
> against views
> 9. Publish information about database events, user
> events, and SQL statements to subscribing applications
> 10. Auditing (FGA replaces many uses)
> 11. enforce complex security rules
> 12. Modify table data when DML statements are issued
> against views
> 3. Subtopics
> 1. mutating tables
> 2. table/system/DDL
> 3. row /statement / instead of
> 4. before/ after
> 5. insert /update/delete
> 6. NEW / OLD
> 7. how to find stored code
> 2.
> Day 7 (ch 14, 21) - Creating Packages and Oracle supplied
> packages
> 1. Main Topics
> 1. packages
> 2. oracle supplied packages
> 2. Why Use? Common Uses?
> 1. Define related types/cursors/constants/exceptions/
> procedures/functions in one place
> 2. Loaded once instead of for each procedure
> 3. Encapsulation - members can be public/private.
> separate interface/implementation
> 4. initialization -1st time package used in session can
> run initialization code
> 3. Subtopics
> 1. do interface 1st.
> 2. put types in package
> 3. overloading
> 4. set/get on vars
> 5. oracle packages: regular expressions, UTL_FILE,
> DBMS_JOB, DBMS_FGA (?)
> 3.
> Day 8 (ch 10, 11, 20) - Advanced Exceptions and Dynamic SQL
> 1. Topics
> 1. Advanced Exceptions
> 2. Execute immediate
> 3. bind vars
> 4. Autonomous transactions
> 2. Why execute immediate? Common Uses?
> 1. When you don't know what SQL will look like
> 3. Why bind vars? Common Uses
> 1. automatic w/ most SQL in PL/SQL
> 2. for efficiency especially w/ execute immediate.
> 4. Why Autonomous transactions? How used?
> 1. used when absolutely necessary
> 2. error logging
> 5. Subtopics
> 1. error handling conventions/strategies / packages to
> handle errors
> 2. preventing injection attacks (and assert)
> 3. parsing SQL and efficiency
> 4. encapsulating dynamic SQL/catching errors
> 5. invoker rights/when to use
> 6. EXCEPTION_INIT
> 7. RAISE_APPLICATION_ERROR
> 8. AUTONOMOUS_TRANSACTION
> 9. SQLERRM /SQLCODE
> 4.
> Day 9 (ch 18,19) - Collections and Records
> 1. Topics
> 1. Collections
> 2. Records
> 2. Why advanced data types? Common Uses?
> 1. easier to do certain algorithms
> 2. pass data back via collections from procedure
> 3. Subtopics
>
> --
> http://www.douglassdavis.com

Comments in no particular order.

1. Why are you teaching CONSTANTS with PLS_INTEGER rather than with VARIABLES?
2. Why is "Anchored Types" in there multiple times.

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 CURSORS. 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, and RAISE_APPLICATION_ERROR.

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.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org