Oracle Database Advanced Application Developer’s Guide Developer
User Manual:
Open the PDF directly: View PDF
Page Count: 576 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- Contents
- List of Examples
- List of Figures
- List of Tables
- Preface
- What's New in Application Development?
- Part I SQL for Application Developers
- 1 Using SQL Data Types in Database Applications
- Overview of SQL Data Types
- Representing Character Data
- Representing Numeric Data
- Representing Date and Time Data
- Representing Specialized Data
- Representing Conditional Expressions as Data
- Identifying Rows by Address
- How Oracle Database Converts Data Types
- Metadata for SQL Operators and Functions
- 2 SQL Processing for Application Developers
- Description of SQL Statement Processing
- Grouping Operations into Transactions
- Ensuring Repeatable Reads with Read-Only Transactions
- Using Cursors
- Locking Tables Explicitly
- Using Oracle Lock Management Services (User Locks)
- Using Serializable Transactions for Concurrency Control
- Autonomous Transactions
- Resuming Execution After Storage Allocation Errors
- 3 Using Regular Expressions in Database Applications
- 4 Using Indexes in Database Applications
- 5 Maintaining Data Integrity in Database Applications
- Enforcing Business Rules with Constraints
- Enforcing Business Rules with Both Constraints and Application Code
- Creating Indexes for Use with Constraints
- When to Use NOT NULL Constraints
- When to Use Default Column Values
- Choosing a Primary Key for a Table (PRIMARY KEY Constraint)
- When to Use UNIQUE Constraints
- Enforcing Referential Integrity with FOREIGN KEY Constraints
- Minimizing Space and Time Overhead for Indexes Associated with Constraints
- Guidelines for Indexing Foreign Keys
- Referential Integrity in a Distributed Database
- When to Use CHECK Constraints
- Examples of Defining Constraints
- Enabling and Disabling Constraints
- Modifying Constraints
- Renaming Constraints
- Dropping Constraints
- Managing FOREIGN KEY Constraints
- Viewing Information About Constraints
- Part II PL/SQL for Application Developers
- 6 Coding PL/SQL Subprograms and Packages
- Overview of PL/SQL Units
- Anonymous Blocks
- Stored PL/SQL Units
- Naming Subprograms
- Subprogram Parameters
- Creating Subprograms
- Altering Subprograms
- Dropping Subprograms and Packages
- External Subprograms
- PL/SQL Function Result Cache
- PL/SQL Packages
- PL/SQL Object Size Limits
- Creating Packages
- Naming Packages and Package Objects
- Package Invalidations and Session State
- Packages Supplied with Oracle Database
- Overview of Bulk Binding
- When to Use Bulk Binds
- Triggers
- Compiling PL/SQL Subprograms for Native Execution
- Cursor Variables
- Handling PL/SQL Compile-Time Errors
- Handling Runtime PL/SQL Errors
- Debugging Stored Subprograms
- Invoking Stored Subprograms
- Invoking Remote Subprograms
- Invoking Stored PL/SQL Functions from SQL Statements
- Returning Large Amounts of Data from a Function
- Coding Your Own Aggregate Functions
- Overview of PL/SQL Units
- 7 Using PL/Scope
- 8 Using the PL/SQL Hierarchical Profiler
- 9 Developing PL/SQL Web Applications
- Overview of PL/SQL Web Applications
- Implementing PL/SQL Web Applications
- Using mod_plsql Gateway to Map Client Requests to a PL/SQL Web Application
- Using Embedded PL/SQL Gateway
- How Embedded PL/SQL Gateway Processes Client Requests
- Installing Embedded PL/SQL Gateway
- Configuring Embedded PL/SQL Gateway
- Configuring Embedded PL/SQL Gateway: Overview
- Configuring User Authentication for Embedded PL/SQL Gateway
- Configuring Static Authentication with DBMS_EPG
- Configuring Dynamic Authentication with DBMS_EPG
- Configuring Anonymous Authentication with DBMS_EPG
- Determining the Authentication Mode of a DAD
- Creating and Configuring DADs: Examples
- Determining the Authentication Mode for a DAD: Example
- Determining the Authentication Mode for All DADs: Example
- Showing DAD Authorizations that Are Not in Effect: Example
- Examining Embedded PL/SQL Gateway Configuration
- Invoking PL/SQL Stored Subprograms Through Embedded PL/SQL Gateway
- Securing Application Access with Embedded PL/SQL Gateway
- Restrictions in Embedded PL/SQL Gateway
- Using Embedded PL/SQL Gateway: Scenario
- Generating HTML Output with PL/SQL
- Passing Parameters to PL/SQL Web Applications
- Passing List and Dropdown-List Parameters from an HTML Form
- Passing Option and Check Box Parameters from an HTML Form
- Passing Entry-Field Parameters from an HTML Form
- Passing Hidden Parameters from an HTML Form
- Uploading a File from an HTML Form
- Submitting a Completed HTML Form
- Handling Missing Input from an HTML Form
- Maintaining State Information Between Web Pages
- Performing Network Operations in PL/SQL Subprograms
- 10 Developing PL/SQL Server Pages (PSP)
- What Are PL/SQL Server Pages and Why Use Them?
- Prerequisites for Developing and Deploying PL/SQL Server Pages
- PL/SQL Server Pages and the HTP Package
- PL/SQL Server Pages and Other Scripting Solutions
- Developing PL/SQL Server Pages
- Specifying Basic Server Page Characteristics
- Accepting User Input
- Naming the PL/SQL Stored Procedure
- Including the Contents of Other Files
- Declaring Global Variables in a PSP Script
- Specifying Executable Statements in a PSP Script
- Substituting Expression Values in a PSP Script
- Using Quotation Marks and Escaping Strings in a PSP Script
- Including Comments in a PSP Script
- Loading PL/SQL Server Pages into the Database
- Querying PL/SQL Server Page Source Code
- Running PL/SQL Server Pages Through URLs
- Examples of PL/SQL Server Pages
- Debugging PL/SQL Server Pages
- Putting PL/SQL Server Pages into Production
- 11 Using Continuous Query Notification (CQN)
- Object Change Notification (OCN)
- Query Result Change Notification (QRCN)
- Events that Generate Notifications
- Notification Contents
- Good Candidates for CQN
- Creating CQN Registrations
- PL/SQL CQN Registration Interface
- CQN Registration Options
- Prerequisites for Creating CQN Registrations
- Queries that Can Be Registered for Object Change Notification (OCN)
- Queries that Can Be Registered for Query Result Change Notification (QRCN)
- Using PL/SQL to Register Queries for CQN
- Best Practices for CQN Registrations
- Troubleshooting CQN Registrations
- Querying CQN Registrations
- Interpreting Notifications
- Deleting Registrations
- Configuring CQN: Scenario
- Part III Advanced Topics for Application Developers
- 12 Using Oracle Flashback Technology
- Overview of Oracle Flashback Technology
- Configuring Your Database for Oracle Flashback Technology
- Using Oracle Flashback Query (SELECT AS OF)
- Using Oracle Flashback Version Query
- Using Oracle Flashback Transaction Query
- Using Oracle Flashback Transaction Query with Oracle Flashback Version Query
- Using DBMS_FLASHBACK Package
- Using Flashback Transaction
- Using Flashback Data Archive (Oracle Total Recall)
- Creating a Flashback Data Archive
- Altering a Flashback Data Archive
- Dropping a Flashback Data Archive
- Specifying the Default Flashback Data Archive
- Enabling and Disabling Flashback Data Archive
- DDL Statements on Tables Enabled for Flashback Data Archive
- Viewing Flashback Data Archive Data
- Flashback Data Archive Scenarios
- General Guidelines for Oracle Flashback Technology
- Performance Guidelines for Oracle Flashback Technology
- 13 Choosing a Programming Environment
- Overview of Application Architecture
- Overview of the Program Interface
- Overview of PL/SQL
- Overview of Oracle Database Java Support
- Choosing PL/SQL or Java
- Overview of Precompilers
- Overview of OCI and OCCI
- Choosing a Precompiler or OCI
- Overview of Oracle Data Provider for .NET (ODP.NET)
- Overview of OraOLEDB
- Overview of Oracle Objects for OLE (OO4O)
- 14 Developing Applications with Multiple Programming Languages
- Overview of Multilanguage Programs
- What Is an External Procedure?
- Overview of Call Specification for External Procedures
- Loading External Procedures
- Publishing External Procedures
- Publishing Java Class Methods
- Publishing External C Procedures
- Locations of Call Specifications
- Example: Locating a Call Specification in a PL/SQL Package
- Example: Locating a Call Specification in a PL/SQL Package Body
- Example: Locating a Call Specification in an ADT Specification
- Example: Locating a Call Specification in an ADT Body
- Example: Java with AUTHID
- Example: C with Optional AUTHID
- Example: Mixing Call Specifications in a Package
- Passing Parameters to External C Procedures with Call Specifications
- Running External Procedures with CALL Statements
- Handling Errors and Exceptions in Multilanguage Programs
- Using Service Routines with External C Procedures
- Doing Callbacks with External C Procedures
- 15 Developing Applications with Oracle XA
- X/Open Distributed Transaction Processing (DTP)
- Oracle XA Library Subprograms
- Developing and Installing XA Applications
- DBA or System Administrator Responsibilities
- Application Developer Responsibilities
- Defining the xa_open String
- Using Oracle XA with Precompilers
- Using Oracle XA with OCI
- Managing Transaction Control with Oracle XA
- Examples of Precompiler Applications
- Migrating Precompiler or OCI Applications to TPM Applications
- Managing Oracle XA Library Thread Safety
- Using the DBMS_XA Package
- Troubleshooting XA Applications
- Oracle XA Issues and Restrictions
- 16 Developing Applications with the Publish-Subscribe Model
- 17 Using the Identity Code Package
- Identity Concepts
- What is the Identity Code Package?
- Using the Identity Code Package
- Storing RFID Tags in Oracle Database Using MGD_ID ADT
- Creating a Table with MGD_ID Column Type and Storing EPC Tag Encodings in the Column
- Constructing MGD_ID Objects to Represent RFID Tags
- Constructing an MGD_ID Object (SGTIN-64) Passing in the Category ID and a List of Components
- Constructing an MGD_ID object (SGTIN-64) and Passing in the Category ID, the Tag Identifier, and the List of Additional Required Parameters
- Constructing an MGD_ID object (SGTIN-64) and Passing in the Category Name, Category Version (if null, then the latest version is used), and a List of Components
- Constructing an MGD_ID object (SGTIN-64) and Passing in the Category Name and Category Version, the Tag Identifier, and the List of Additional Required Parameters
- Inserting an MGD_ID Object into a Database Table
- Querying MGD_ID Column Type
- Building a Function-Based Index Using the Member Functions of the MGD_ID Column Type
- Using MGD_ID ADT Functions
- Defining a Category of Identity Codes and Adding Encoding Schemes to an Existing Category
- Storing RFID Tags in Oracle Database Using MGD_ID ADT
- Identity Code Package Types
- DBMS_MGD_ID_UTL Package
- Identity Code Metadata Tables and Views
- Electronic Product Code (EPC) Concepts
- RFID Technology and EPC v1.1 Coding Schemes
- Product Code Concepts and Their Current Use
- Electronic Product Code (EPC)
- Global Trade Identification Number (GTIN) and Serializable Global Trade Identification Number (SGTIN)
- Serial Shipping Container Code (SSCC)
- Global Location Number (GLN) and Serializable Global Location Number (SGLN)
- Global Returnable Asset Identifier (GRAI)
- Global Individual Asset Identifier (GIAI)
- RFID EPC Network
- Oracle Database Tag Data Translation Schema
- 18 Schema Object Dependency
- Overview of Schema Object Dependencies
- Querying Object Dependencies
- Object Status
- Invalidation of Dependent Objects
- Guidelines for Reducing Invalidation
- Object Revalidation
- Name Resolution in Schema Scope
- Local Dependency Management
- Remote Dependency Management
- Remote Procedure Call (RPC) Dependency Management
- Shared SQL Dependency Management
- 19 Edition-Based Redefinition
- Editions
- Editioning Views
- Crossedition Triggers
- Displaying Information About Editions, Editioning Views, and Crossedition Triggers
- Using Edition-Based Redefinition to Upgrade an Application
- Preparing Your Application to Use Editioning Views
- Procedure for Edition-Based Redefinition Using Only Editions
- Procedure for Edition-Based Redefinition Using Editioning Views
- Procedure for Edition-Based Redefinition Using Crossedition Triggers
- Rolling Back the Application Upgrade
- Reclaiming Space Occupied by Unused Table Columns
- Example: Using Edition-Based Redefinition to Upgrade an Application
- A Multithreaded extproc Agent
- Why Use the Multithreaded extproc Agent?
- Multithreaded extproc Agent Architecture
- Administering the Multithreaded extproc Agent
- Agent Control Utility (agtctl) Commands
- Using agtctl in Single-Line Command Mode
- Setting Configuration Parameters for a Multithreaded extproc Agent
- Starting a Multithreaded extproc Agent
- Shutting Down a Multithreaded extproc Agent
- Examining the Value of Configuration Parameters
- Resetting a Configuration Parameter to Its Default Value
- Deleting an Entry for a Specific SID from the Control File
- Requesting Help
- Using Shell Mode Commands
- Configuration Parameters for Multithreaded extproc Agent Control
- Index