Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with Packages

by Steven Feuerstein
Released October 1996
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565922389

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Steven Feuerstein's first book, Oracle PL/SQL Programming, has become the classic reference to PL/SQL, Oracle's procedural extension to its SQL language. His new book looks thoroughly at one especially advanced and powerful part of the PL/SQL language -- the package. The use of packages can dramatically improve your programming productivity and code quality, while preparing you for object-oriented development in Oracle technology. In this book, Feuerstein explains how to construct packages -- and how to build them the right way. His "best practices" for building packages will transform the way you write packages and help you get the most out of the powerful, but often poorly understood, PL/SQL language. Much more than a book, Advanced Oracle PL/SQL Programming with Packages comes with a PC diskette containing a full-use software companion. Developed by Feuerstein, RevealNet's PL/Vision Lite is the first of its kind for PL/SQL developers: a library of thirty-plus PL/SQL packages. The packages solve a myriad of common programming problems and vastly accelerate the development of modular and maintainable applications. The packages provided in PL/Vision Lite fall into three categories:

  • Building block packages: low-level development enhancers, including string parsers, a list manager, and an interface to PL/SQL tables.

  • Developer utilities: programs that improve your PL/SQL development environment, including a code generator, a powerful substitute for SHOW ERRORS, and an online help delivery mechanism.

  • Plug-and-play components: pieces of code that can be used as is in your own applications, including a high-level exception handler mechanism and a generic, reusable logging mechanism.

In addition to describing the details of these packages, this book explores why and how they were built the way they were. You'll come away with an increased appreciation of the PL/SQL language and the power of packages. Most importantly, you'll be ready and eager to put that power to use immediately in your own applications.

Table of contents

  1. Copyright
  2. Dedication
  3. Foreword
  4. Preface
    1. Objectives of This Book
    2. Structure of This Book
    3. Conventions Used in This Book
    4. About the Disk
    5. About PL/Vision
    6. Comments and Questions
    7. Acknowledgments
  5. Working With Packages
    1. PL/SQL Packages
      1. What Is a PL/SQL Package?
      2. What Are the Types and Layers of Packages?
      3. What Are the Benefits of Packages?
      4. Using Packages
      5. Types of Packages
      6. Building Packages
    2. Best Practices for Packages
      1. Starting With Packages
      2. Using Effective Coding Style for Packages
      3. Selecting Package Names
      4. Organizing Package Source Code
      5. Constructing the Optimal Interface to Your Package
      6. Building Flexibility Into Your Packages
      7. Building Windows Into Your Packages
      8. Overloading for Smart Packages
      9. Modularizing for Maintainable Packages
      10. Hiding Package Data
      11. Simultaneous Construction of Multiple Packages
    3. The PL/SQL Development Spiral
      1. The Basic Problem
      2. Adding Value
      3. Supplying Backward Compatibility
      4. Improving the User Interface
      5. Rough Waters Ahead
      6. Building a Structured Function
      7. Handling Program Assumptions
      8. Broadening the Scope
      9. Considering Implementation Options
      10. Choosing the Best Performer
      11. Don't Forget Backward Compatibility
      12. Obliterating the Literals
      13. Glancing Backward, Looking Upward
  6. PL/Vision Overview
    1. Getting Started with PL/Vision
      1. What Is PL/Vision?
      2. PL/Vision Package Bundles
      3. Installation Instructionss
      4. Installing Online Help for PL/Vision
      5. Using Online Help
      6. Summary of Files on Disk
    2. PL/Vision Package Specifications
      1. Common Package Elements
      2. p: a DBMS_OUTPUT Substitute
      3. PLV: Top-Level Constants and Functions
      4. PLVcase: PL/SQL Code Conversion
      5. PLVcat: PL/SQL Code Cataloguing
      6. PLVchr: Operations on Single Characters
      7. PLVcmt: Commit Processing
      8. PLVddd: DDL Syntax Dump
      9. PLVdyn: Dynamic SQL Operations
      10. PLVexc: Exception Handling
      11. PLVfile: Operating System I/O Manager
      12. PLVfk: Foreign Key Interface
      13. PLVgen: PL/SQL Code Generator
      14. PLVhlp: Online Help Architechture
      15. PLVio: Input/Output Processing
      16. PLVlex: Lexical Analysis
      17. PLVlog: Logging Facility
      18. PLVlst: List Manager
      19. PLVmsg: Message Handling
      20. PLVobj: Object Interface
      21. PLVprs: String Parsing
      22. PLVprsps: PL/SQL Source Code Parsing
      23. PLVrb: Rollback Processing
      24. PLVstk: Stack Manager
      25. PLVtab: Table Interface
      26. PLVtkn: Token Table Interface
      27. PLVtmr: Program Performance Analyzer
      28. PLVtrc: Trace Facility
      29. PLVvu: Code and Error Viewing
  7. Building Block Packages
    1. PLV: Top-Level Constants and Functions
      1. Null Substitution Value
      2. Setting the PL/Vision Date Mask
      3. Assertion Routines
      4. PLV Utilities
      5. The Predefined Datatypes
      6. The Predefined Constants
    2. p: A Powerful Substitute for DBMS_OUTPUT
      1. Using the l Procedure
      2. The Line Separator
      3. The Output Prefix
      4. Controlling Output from p
    3. PLVtab: Easy Access to PL/SQL Tables
      1. Using PLVtab-Based PL/SQL Table Types
      2. Displaying PLVtab Tables
      3. Showing Header Toggle
      4. Showing Row Number Toggle
      5. Setting the Display Prefix
      6. Emptying Tables with PLVtab
      7. Implementing PLVtab.display
    4. PLVmsg: Single-Sourcing PL/SQL Message Text
      1. PLVmsg Data Structures
      2. Storing Message Text
      3. Retrieving Message Text
      4. The Restriction Toggle
      5. Integrating PLVmsg with Error Handling
      6. Implementing load_ from_dbms
    5. PLVprs, PLVtkn, and PLVprsps: Parsing Strings
      1. PLVprs: Useful String Parsing Extensions
      2. PLVtkn: Managing PL/SQL Tokens
      3. PLVprsps: Parsing PL/SQL Strings
    6. PLVobj: A Packaged Interface to ALL_OBJECTS
      1. Why PLVobj?
      2. ALL_OBJECTS View
      3. Setting the Current Object
      4. Accessing ALL_OBJECTS
      5. Binding Objects to a Dynamic Cursor
      6. Populating a PL/SQL Table with Object Names
      7. A Programmatic Cursor FOR Loop
      8. Tracing PLVobj Activity
    7. PLVio: Reading and Writing PL/SQL Source Code
      1. Why PLVio?
      2. Code Repositories Supported by PLVio
      3. Managing the Source Repository
      4. The Source WHERE Clause
      5. Managing the Target Repository
      6. Reading From the Source
      7. Writing to the Target
      8. Saving and Restoring Settings
      9. Cleaning Up Source and Target
    8. PLVfile: Reading and Writing Operating System Files
      1. A Review of UTL_FILE
      2. Specifying the File in PLVfile
      3. Creating and Checking Existence of Files
      4. Opening and Closing Files
      5. Reading From a File
      6. Writing to a File
      7. Copying File Contents
      8. Displaying File Contents
      9. Handling File Errors with PLVfile
      10. Tracing PLVfile Activity
  8. Developer Utility Packages
    1. PLVtmr: Analyzing Program Performance
      1. Toggling the Timer
      2. Capturing the Start Time
      3. Retrieving and Displaying the Elapsed Time
      4. Using PLVtmr in Scripts
    2. PLVvu: Viewing Source Code and Compile Errors
      1. Compiling PL/SQL Code in SQL*Plus
      2. Displaying Compile Errors
      3. Displaying Source Code
      4. Implementing PLVvu
    3. PLVgen: Generating PL/SQL Programs
      1. Options for Best Practices
      2. Code Generated by PLVgen
      3. Modifying PLVgen Behavior
      4. Implementing PLVgen
    4. PLVhlp: Online Help for PL/SQL Programs
      1. Who Needs Online Help?
      2. Current Sources of Information
      3. What Is "Online Help" for Stored Code?
      4. Using PLVhlp
      5. Implementing PLVhlp
    5. PLVcase and PLVcat: Converting and Analyzing PL/SQL Code
      1. PLVcase: Converting the Case of PL/SQL Programs
      2. PLVcat: Cataloguing PL/SQL Source Code
  9. Plug-and-Play Packages
    1. PLVdyn and PLVfk: Dynamic SQL and PL/SQL
      1. About Plug-and-Play
      2. Declarative Programming in PL/SQL
      3. The Dynamic Packages of PL/Vision
      4. PLVdyn: A Code Layer over DBMS_SQL
      5. DML Operations
      6. PLVfk: Generic Foreign Key Lookups
    2. PLVcmt and PLVrb: Commit and Rollback Processing
      1. PLVcmt: Enhancing Commit Processing
      2. PLVrb: Performing Rollbacks
    3. PLVlog and PLVtrc: Logging and Tracing
      1. PLVlog: Logging Activity in PL/SQL Programs
      2. PLVtrc: Tracing Execution of PL/SQL Programs
    4. PLVexc: Exception Handling
      1. The Challenge of Exception Handling
      2. Application-Specific Exception Packages
      3. Implementing PLVexc
  10. Testing Your Knowledge
    1. Appendix: PL/SQL Exercises
      1. Exercises
      2. Solutions
  11. Colophon
  12. Index

Product information

  • Title: Advanced Oracle PL/SQL Programming with Packages
  • Author(s): Steven Feuerstein
  • Release date: October 1996
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565922389