Oracle Database 10g PL/SQL Programming

Oracle Database 10g PL/SQL Programming

by Scott Urman, Ron Hardman, Michael McLaughlin
Released October 2004
Publisher(s): Oracle Press
ISBN: 9780071502283

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.

Start your free trial

Book description

Create dynamic client/server applications using PL/SQL and the comprehensive information contained in this authoritative volume. Expert Oracle insiders cover the features of PL/SQL, explain scripting semantics and syntax, and fully detail the PL/SQL functionality of Oracle Database 10g. You’ll learn how to write powerful PL/SQL programs, interact with Oracle databases, perform complex calculations, and handle error conditions.

Table of contents

  1. Cover Page
  2. Oracle Database 10g PL/SQL Programming
  3. Copyright Page
  4. About the Authors
  5. Dedication Page
  6. Contents at a Glance
  7. Contents
  8. Acknowledgments
  9. Introduction
  10. PART I Introduction
    1. 1 Introduction to PL/SQL
      1. Introduction to Programming Languages
        1. Note to Beginning Programmers
      2. PL/What?
        1. Structured Query Language (SQL)
        2. Relational Database Overview
        3. PL/SQL vs. SQL
        4. PL/SQL vs. Java
        5. PL/SQL History and Features
      3. Language Fundamentals
        1. Anonymous Blocks
        2. Procedures
        3. Functions
        4. Packages
        5. Object Types
      4. PL/SQL Statement Processing
        1. Interpreted
        2. Native Compilation
      5. Getting the Most from This Book
        1. Audience
        2. Objective
        3. Scope
        4. Assumptions
        5. Conventions
        6. Examples
      6. Summary
    2. 2 Using SQL*Plus and JDeveloper
      1. SQL*Plus
        1. Connecting to the Instance
        2. Testing the Connection
        3. Using SQL*Plus
        4. Changing SQL*Plus Session Settings
        5. Running a Script from a File
        6. Output to the Screen Using SQL*Plus and PL/SQL
      2. JDeveloper
        1. Installing JDeveloper
        2. Working with PL/SQL in JDeveloper
      3. Summary
    3. 3 PL/SQL Basics
      1. The PL/SQL Block
        1. The Basic Structure
        2. Anonymous Blocks
        3. Named Blocks
        4. Nested Blocks
        5. Triggers
        6. Object Types
      2. Language Rules and Conventions
        1. Lexical Units
      3. PL/SQL Data Types
        1. Scalar
        2. Character/String
        3. NUMBER Data Type
        4. Boolean
        5. Date/Time
        6. Composite
        7. Reference
        8. LOB
      4. Using Variables
        1. %TYPE
        2. %ROWTYPE
        3. Variable Scope
        4. Bind Variables
      5. Hiding Code
      6. Expressions
        1. Assignment Operator
        2. Concatenation Operator
      7. Controlling Program Flow
        1. Conditional Evaluation
        2. Circular Execution
        3. Sequential Navigation using GOTO
      8. Summary
    4. 4 Using SQL with PL/SQL
      1. Transaction Processing
        1. Transactions and Locking
        2. Autonomous Transactions
        3. Set Transaction
      2. Retrieving Data
        1. SQL SELECT Statement
        2. Pattern Matching
        3. Information Retrieval
      3. Cursors
        1. How Cursors Work
        2. Explicit Cursors
        3. Implicit Cursors
        4. Cursor Variables
        5. Cursor Subqueries
        6. Open Cursors
      4. DML and DDL
        1. Pre-Compilation
        2. Manipulating Data with DML
        3. DELETE
        4. Introduction to Dynamic SQL
      5. Using ROWID and ROWNUM
        1. ROWID
        2. ROWNUM
      6. Built-in SQL Functions
        1. Character Functions
        2. Numeric Functions
        3. Date Functions
        4. Conversion Functions
        5. Error Functions
        6. Other Functions
      7. Summary
    5. 5 Records
      1. Introducing Records
        1. What Is a Record?
        2. Working with Records
        3. Defining Record Types
        4. Defining and Using Record Types as Formal Parameters
        5. Defining and Using Object Types as Parameters
        6. Returning Record Types from Functions
        7. Defining and Using Record Types as Return Values
        8. Defining and Using Object Types as Return Values
        9. Verifying Work with Record Types
      2. Summary
    6. 6 Collections
      1. Introducing Collections
        1. What Is a Collection?
      2. Working with Collections
        1. Working with Varrays
        2. Working with Nested Tables
        3. Working with Associative Arrays
      3. Oracle 10g Collection API
        1. COUNT Method
        2. DELETE Method
        3. EXISTS Method
        4. EXTEND Method
        5. FIRST Method
        6. LAST Method
        7. LIMIT Method
        8. NEXT Method
        9. PRIOR Method
        10. TRIM Method
      4. Summary
    7. 7 Error Handling
      1. What Is an Exception?
        1. Declaring Exceptions
        2. Raising Exceptions
        3. Handling Exceptions
        4. The EXCEPTION_INIT Pragma
        5. Using RAISE_APPLICATION_ERROR
      2. Exception Propagation
        1. Exceptions Raised in the Executable Section
        2. Exceptions Raised in the Declarative Section
        3. Exceptions Raised in the Exception Section
      3. Exception Guidelines
        1. Scope of Exceptions
        2. Avoiding Unhandled Exceptions
        3. Masking Location of the Error
        4. Exceptions and Transactions
      4. Summary
    8. 8 Creating Procedures, Functions, and Packages
      1. Procedures and Functions
        1. Subprogram Creation
        2. Subprogram Parameters
        3. The CALL Statement
        4. Procedures vs. Functions
      2. Packages
        1. Package Specification
        2. Package Body
        3. Packages and Scope
        4. Overloading Packaged Subprograms
        5. Package Initialization
      3. Summary
    9. 9 Using Procedures, Functions, and Packages
      1. Subprogram Locations
        1. Stored Subprograms and the Data Dictionary
        2. Local Subprograms
        3. Stored vs. Local Subprograms
      2. Considerations of Stored Subprograms and Packages
        1. Subprogram Dependencies
        2. Package Run-Time State
        3. Privileges and Stored Subprograms
      3. Stored Functions and SQL Statements
        1. Single-Valued Functions
        2. Multiple-Valued Functions
      4. Native Compilation
      5. Pinning in the Shared Pool
        1. KEEP
        2. UNKEEP
        3. SIZES
        4. ABORTED_REQUEST_THRESHOLD
        5. The PL/SQL Wrapper
      6. Summary
    10. 10 Database Triggers
      1. Types of Triggers
        1. DML Triggers
        2. Instead-of Triggers
        3. System Triggers
      2. Creating Triggers
        1. Creating DML Triggers
        2. Creating Instead-of Triggers
        3. Creating System Triggers
        4. Other Trigger Issues
        5. Triggers and the Data Dictionary
      3. Mutating Tables
        1. Mutating Table Example
        2. Workaround for the Mutating Table Error
      4. Summary
  11. PART II Advanced PL/SQL Features
    1. 11 Intersession Communication
      1. Introducing Intersession Communication
        1. Requiring Permanent or Semipermanent Structures
        2. Not Requiring Permanent or Semipermanent Structures
      2. The DBMS_PIPE Built-in Package
        1. Introducing the DBMS_PIPE Package
        2. Defining the DBMS_PIPE Package
        3. Working with the DBMS_PIPE Package
      3. DBMS_ALERT Built-in Package
        1. Introducing the DBMS_ALERT Package
        2. Defining the DBMS_ALERT Package
        3. Working with the DBMS_ALERT Package
      4. Summary
    2. 12 External Routines
      1. Introducing External Procedures
      2. Working with External Procedures
        1. Defining the extproc Architecture
        2. Defining extproc Oracle Net Services Configuration
        3. Defining the Multithreaded External Procedure Agent
        4. Working with a C Shared Library
        5. Working with a Java Shared Library
      3. Troubleshooting the Shared Library
        1. Configuration of the Listener or Environment
        2. Configuration of the Shared Library or PL/SQL Library Wrapper
      4. Summary
    3. 13 Dynamic SQL
      1. Introducing Dynamic SQL
      2. Working with Native Dynamic SQL
        1. Working with DDL and DML Without Bind Variables
        2. Working with DML and a Known List of Bind Variables
        3. Working with DQL
      3. Working with the Oracle DBMS_SQL Built-in Package
        1. Working with DDL and DML Without Bind Variables
        2. Working with DML and a Known List of Bind Variables
        3. Working with DQL
      4. Summary
    4. 14 Introduction to Objects
      1. Introduction to Object-Oriented Programming
        1. Data and Procedural Abstraction
      2. Object Type Overview
      3. Creating Object Types
        1. Object Type Specification
        2. Object Type Body
      4. Object Type Inheritance
        1. Dynamic Method Dispatch
      5. Attribute Chaining
      6. Making Changes
        1. Type Evolution
      7. Summary
    5. 15 Objects in the Database
      1. Introduction to Objects in the Database
        1. Object Tables
        2. Column Objects
        3. Object Views
      2. Accessing Persistent Objects Using SQL and PL/SQL
        1. Object Tables
        2. Accessing Column Objects
        3. Accessing Object Views
        4. Object Related Functions and Operators
      3. Maintaining Persistent Objects
        1. Type Evolution
      4. Summary
    6. 16 Large Objects
      1. Introduction to Large Objects
        1. Features Comparison
        2. Types of LOBs
        3. LOB Structure
        4. Internal LOB Storage
        5. External LOB Storage
        6. Temporary LOB Storage
        7. Migrating from LONGs to LOBs
      2. LOBs and SQL
        1. SQL for Internal Persistent LOBs
        2. External LOB – BFILE
      3. LOBs and PL/SQL
        1. DBMS_LOB
      4. Performance Considerations
        1. Returning Clause
      5. Summary
    7. 17 Scheduling Tasks
      1. Introducing DBMS_JOB
        1. SUBMIT
        2. BROKEN
        3. RUN
        4. CHANGE
        5. REMOVE
      2. Oracle Scheduler
        1. Terminology
        2. Using DBMS_SCHEDULER
        3. Migrating from DBMS_JOB
        4. Removing a Job
      3. Summary
  12. PART III Appendixes
    1. A PL/SQL Reserved Words
      1. Table of Reserved Words
    2. B Guide to Supplied Packages
      1. SYS-Owned Built-in Packages
        1. DBMS_ADVANCED_REWRITE
        2. DBMS_ADVISOR
        3. DBMS_ALERT
        4. DBMS_APPLICATION_INFO
        5. DBMS_APPLY_ADM
        6. DBMS_AQ
        7. DBMS_BACKUP_RESTORE
        8. DBMS_CAPTURE_ADM
        9. DBMS_CRYPTO
        10. DBMS_DATA_MINING, DBMS_DATA_MINING_TRANSFORM
        11. DBMS_DATAPUMP
        12. DBMS_DDL
        13. DBMS_DEBUG
        14. DBMS_DEFER
        15. DBMS_DESCRIBE
        16. DBMS_DIMENSION
        17. DBMS_DISTRIBUTED_TRUST_ADMIN
        18. DBMS_FGA
        19. DBMS_FILE_TRANSFER
        20. DBMS_FLASHBACK
        21. DBMS_HS
        22. DBMS_HS_PASSTHROUGH
        23. DBMS_JAVA
        24. DBMS_JOB
        25. DBMS_LDAP
        26. DBMS_LOB
        27. DBMS_LOCK
        28. DBMS_LOGMNR
        29. DBMS_METADATA
        30. DBMS_MONITOR
        31. DBMS_OBFUSCATION_TOOLKIT
        32. DBMS_ODCI
        33. DBMS_OFFLINE_OG
        34. DBMS_OLAP
        35. DBMS_OUTLN
        36. DBMS_OUTLN_EDIT
        37. DBMS_OUTPUT
        38. DBMS_PIPE
        39. DBMS_PROFILER
        40. DBMS_PROPAGATION_ADM
        41. DBMS_RANDOM
        42. DBMS_REDEFINITION
        43. DBMS_REFRESH
        44. DBMS_REPAIR
        45. DBMS_REPCAT
        46. DBMS_RESOURCE_MANAGER/DBMS_RESOURCE_MANAGER_PRIVS
        47. DBMS_RESUMABLE
        48. DBMS_ROWID
        49. DBMS_RULE, DBMS_RULE_ADM
        50. DBMS_SCHEDULER
        51. DBMS_SCHEMA_COPY
        52. DBMS_SERVER_ALERT
        53. DBMS_SERVICE
        54. DBMS_SESSION
        55. DBMS_SHARED_POOL
        56. DBMS_SPACE
        57. DBMS_SQL
        58. DBMS_SQLTUNE
        59. DBMS_STANDARD, STANDARD
        60. DBMS_STAT_FUNCS
        61. DBMS_STATS
        62. DBMS_SUMMARY
        63. DBMS_TRACE
        64. DBMS_TRANSACTION
        65. DBMS_TRANSFORM
        66. DBMS_TYPES
        67. DBMS_UTILITY
        68. DBMS_WARNING
        69. DBMS_WORKLOAD_REPOSITORY
        70. DBMS_XMLGEN, DBMS_XMLQUERY
        71. DBMS_XPLAN
        72. UTL_COLL
        73. UTL_COMPRESS
        74. UTL_DBWS
        75. UTL_ENCODE
        76. UTL_FILE
        77. UTL_HTTP
        78. UTL_I18N
        79. UTL_INADDR
        80. UTL_LMS
        81. UTL_MAIL
        82. UTL_RAW
        83. UTL_RECOMP
        84. UTL_REF
        85. UTL_SMTP
        86. UTL_TCP
        87. UTL_URL
      2. CTXSYS-Owned Built-in Packages
        1. CTX_ADM
        2. CTX_CLS
        3. CTX_DDL
        4. CTX_DOC
        5. CTX_OUTPUT
        6. CTX_QUERY
        7. CTX_REPORT
        8. CTX_THES
  13. Index

Product information

  • Title: Oracle Database 10g PL/SQL Programming
  • Author(s): Scott Urman, Ron Hardman, Michael McLaughlin
  • Release date: October 2004
  • Publisher(s): Oracle Press
  • ISBN: 9780071502283