Oracle PL/SQL Programming

Name: Oracle PL/SQL Programming | A Developer's Workbook
Brand: O'Reilly
Price: 91.99 EUR
Availability: OnlineOnly

A Developer's Workbook

Steven Feuerstein(Autor*in)

O'Reilly (Verlag)

1. Auflage

Erschienen am 22. August 2005

1200 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-0-596-55314-2 (ISBN)

91,99 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Zur neuen Auflage

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Intro
Table of Contents
Preface
Objectives of This Book
Structure of This Book
About the Contents
What This Book Does Not Cover
Conventions Used in This Book
Which Platform or Version?
About the Code
Safari Enabled
Comments and Questions
Acknowledgments
Part I
Introduction to PL/SQL
What Is PL/SQL?
The Origins of PL/SQL
The Early Years of PL/SQL
Improved Application Portability
Improved Execution Authority and Transaction Integrity
Humble Beginnings, Steady Improvement
The Significance of Oracle Database 10g PL/SQL
So This Is PL/SQL
Integration with SQL
Control and Conditional Logic
When Things Go Wrong
About PL/SQL Versions
Oracle Database 10g New Features
Optimized compiler
Compile-time warnings
Conditional compilation
Support for nonsequential collections in FORALL
Improved datatype support
Backtrace an exception to its line number
Set operators for nested tables
Support for regular expressions
Programmer-defined quoting mechanism
Many new built-in packages
Resources for PL/SQL Developers
The O'Reilly PL/SQL Series
PL/SQL on the Internet
Some Words of Advice
Don't Be in Such a Hurry!
Don't Be Afraid to Ask for Help
Take a Creative, Even Radical Approach
Creating and Running PL/SQL Code
SQL*Plus
Starting Up SQL*Plus
Running a SQL Statement
Running a PL/SQL Program
Running a Script
What Is the "Current Directory?"
Other SQL*Plus Tasks
Setting your preferences
Saving output to a file
Exiting SQL*Plus
Editing a statement
Loading your own custom environment automatically on startup
Error Handling in SQL*Plus
Why You Will Love and Hate SQL*Plus
Performing Essential PL/SQL Tasks
Creating a Stored Program
Executing a Stored Program
Showing Stored Programs
Managing Grants and Synonyms for Stored Programs
Dropping a Stored Program
Hiding the Source Code of a Stored Program
Calling PL/SQL from Other Languages
C: Using Oracle's Precompiler (Pro*C)
Java: Using JDBC
Perl: Using Perl DBI and DBD::Oracle
PHP: Using Oracle Extensions
PL/SQL Server Pages
And Where Else?
Language Fundamentals
PL/SQL Block Structure
Anonymous Blocks
Named Blocks
Nested Blocks
Scope
Visibility
"Visible" identifiers
Qualified identifiers
Qualifying identifier names with module names
Nested programs
The PL/SQL Character Set
Identifiers
Reserved Words
Language keywords
Identifiers from STANDARD package
Approaches to avoiding reserved words
Whitespace and Keywords
Literals
NULLs
Embedding Single Quotes Inside a Literal String
Numeric Literals
Boolean Literals
The Semicolon Delimiter
Comments
Single-Line Comment Syntax
Multiline Comment Syntax
The PRAGMA Keyword
Labels
Part II
Conditional and Sequential Control
IF Statements
The IF-THEN Combination
The IF-THEN-ELSE Combination
The IF-THEN-ELSIF Combination
Nested IF Statements
Short-Circuit Evaluation
CASE Statements and Expressions
Simple CASE Statements
Searched CASE Statements
Nested CASE Statements
CASE Expressions
The GOTO Statement
The NULL Statement
Improving Program Readability
Nullifying a Raised Exception
Using NULL After a Label
Iterative Processing with Loops
Loop Basics
Examples of Different Loops
Structure of PL/SQL Loops
The Simple Loop
Terminating a Simple Loop: EXIT and EXIT WHEN
Emulating a REPEAT UNTIL Loop
The Intentionally Infinite Loop
The WHILE Loop
The Numeric FOR Loop
Rules for Numeric FOR Loops
Examples of Numeric FOR Loops
Handling Nontrivial Increments
The Cursor FOR Loop
Example of Cursor FOR Loops
Loop Labels
Tips for Iterative Processing
Use Understandable Names for Loop Indexes
The Proper Way to Say Goodbye
Obtaining Information About FOR Loop Execution
SQL Statement as Loop
Exception Handlers
Exception-Handling Concepts and Terminology
Defining Exceptions
Declaring Named Exceptions
Associating Exception Names with Error Codes
Using EXCEPTION_INIT
Recommended uses of EXCEPTION_INIT
About Named System Exceptions
Scope of an Exception
Raising Exceptions
The RAISE Statement
Using RAISE_APPLICATION_ERROR
Handling Exceptions
Built-in Error Functions
More on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Just the line number, please
Useful applications of SQLERRM
Combining Multiple Exceptions in a Single Handler
Unhandled Exceptions
Propagation of an Unhandled Exception
Losing exception information
Examples of propagation
Continuing Past Exceptions
Writing WHEN OTHERS Handling Code
Building an Effective Error Management Architecture
Decide on Your Error Management Strategy
Organize Use of Application-Specific Error Codes
Work with Your Own Exception "Objects"
Use Standardized Error Management Programs
Create Standard Templates for Common Error Handling
Making the Most of PL/SQL Error Management
Part III
Working with Program Data
Naming Your Program Data
Overview of PL/SQL Datatypes
Character Data
Numbers
Dates, Timestamps, and Intervals
Booleans
Binary Data
ROWIDs
REF Cursors
Internet Datatypes
"Any" Datatypes
User-Defined Datatypes
Declaring Program Data
Declaring a Variable
Declaring Constants
The NOT NULL Clause
Anchored Declarations
Anchoring to Cursors and Tables
Benefits of Anchored Declarations
Synchronization with database columns
Normalization of local variables
Anchoring to NOT NULL Datatypes
Programmer-Defined Subtypes
Conversion Between Datatypes
Implicit Data Conversion
Limitations of implicit conversion
Drawbacks of implicit conversion
Explicit Datatype Conversion
The CHARTOROWID function
The CAST function
The CONVERT function
The HEXTORAW function
The RAWTOHEX function
The ROWIDTOCHAR function
Strings
String Datatypes
The VARCHAR2 Datatype
The CHAR Datatype
String Subtypes
Working with Strings
Specifying String Constants
Using Nonprintable Characters
Concatenating Strings
Dealing with Case
Forcing a string to all upper- or lowercase
Making comparisons case-insensitive
Capitalizing each word in a string
Traditional Searching, Extracting, and Replacing
Padding
Trimming
Regular Expression Searching, Extracting, and Replacing
Detecting a pattern
Locating a pattern
Extracting text matching a pattern
Replacing text (it's magic!)
Groking greediness
Learning more about regular expressions
Working with Empty Strings
Mixing CHAR and VARCHAR2 Values
Database-to-variable conversion
Variable-to-database conversion
String comparisons
Character functions and CHAR arguments
String Function Quick Reference
Numbers
Numeric Datatypes
The NUMBER Type
The PLS_INTEGER Type
The BINARY_INTEGER Type
The BINARY_FLOAT and BINARY_DOUBLE Types
Numeric Subtypes
Number Conversions
The TO_NUMBER Function
Using TO_NUMBER with no format
Using TO_NUMBER with a format model
Passing NLS settings to TO_NUMBER
The TO_CHAR Function
Using TO_CHAR with no format
Using TO_CHAR with a format model
The V format element
Rounding when converting numbers to character strings
Dealing with spaces when converting numbers to character strings
Passing NLS settings to TO_CHAR
Using CAST
Implicit Conversions
Numeric Functions
Rounding and Truncation Functions
Trigonometric Functions
Numeric Function Quick Reference
Dates and Timestamps
Datetime Datatypes
Declaring Datetime Variables
Choosing a Datetime Datatype
Getting the Date and Time
Interval Datatypes
Declaring INTERVAL Variables
When to Use INTERVALs
Finding the difference between two datetime values
Designating periods of time
Datetime Conversions
From Strings to Datetimes
From Datetimes to Strings
Working with Time Zones
Requiring a Format Mask to Match Exactly
Easing Up on Exact Matches
Interpreting Two-Digit Years in a Sliding Window
Converting Time Zones to Character Strings
Padding Output with Fill Mode
Date and Timestamp Literals
Interval Conversions
Going from Numbers to Intervals
Converting Strings to Intervals
Formatting Intervals for Display
Interval Literals
CAST and EXTRACT
The CAST Function
The EXTRACT Function
Datetime Arithmetic
Adding and Subtracting Intervals to/from Datetimes
Computing the Interval Between Two Datetimes
Mixing DATEs and TIMESTAMPs
Adding and Subtracting Intervals
Multiplying and Dividing Intervals
Using Unconstrained INTERVAL Types
Date/Time Functions
Records
Records in PL/SQL
Benefits of Using Records
Data abstraction
Aggregate operations
Leaner, cleaner code
Declaring Records
Programmer-Defined Records
Declaring programmer-defined record TYPEs
Declaring the record
Examples of programmer-defined record declarations
Working with Records
Record-level operations
Field-level operations
Field-level operations with nested records
Field-level operations with package-based records
Comparing Records
Trigger Pseudo-Records
Collections
Collections Overview
Types of Collections
Collections Concepts and Terminology
Collection Examples
Using an associative array
Using a nested table
Using a VARRAY
Where You Can Use Collections
Collections as components of a record
Collections as program parameters
Collections as datatypes of a function's return value
Collection as "columns" in a database table
Collections as attributes of an object type
Choosing a Collection Type
Collection Methods (Built-Ins)
The COUNT Method
Boundary considerations
Exceptions possible
The DELETE Method
Boundary considerations
Exceptions possible
The EXISTS Method
Boundary considerations
Exceptions possible
The EXTEND Method
Boundary considerations
Exceptions possible
The FIRST and LAST Methods
Boundary considerations
Exceptions possible
The LIMIT Method
Boundary considerations
Exceptions possible
The PRIOR and NEXT Methods
Boundary considerations
Exceptions possible
The TRIM Method
Boundary considerations
Exceptions possible
Working with Collections
Declaring Collection Types
Declaring an associative array collection type
Declaring a nested table or VARRAY
Changing nested table of VARRAY characteristics
Declaring and Initializing Collection Variables
Initializing implicitly during direct assignment
Initializing implicitly via FETCH
VARRAY integration
Populating Collections with Data
Using the assignment operator
What row values can I use?
Aggregate assignments
Assigning rows from a relational table
Advantage of nonsequential population of collection
Accessing Data Inside a Collection
Collections of Complex Datatypes
Collections of records
Collections of objects and other complex types
Multilevel collections
Unnamed nested collections: multidimensional arrays
Basic operations
How deeply can I nest collections?
Using String-Indexed Collections
Working with Collections in SQL
The CAST pseudo-function
The MULTISET pseudo-function
The TABLE pseudo-function
Sorting contents of collections
Nested Table Multiset Operations
Testing Equality and Membership of Nested Tables
Checking for Membership of an Element in a Nested Table
Performing High-Level Set Operations
Handling Duplicates in a Nested Table
Maintaining Schema-Level Collections
Necessary Privileges
Collections and the Data Dictionary
Miscellaneous Datatypes
The BOOLEAN Datatype
The RAW Datatype
The UROWID and ROWID Datatypes
Getting at Rowids
Using Rowids
Do rowids ever change?
Using rowids in Oracle Forms
Using rowids in a cursor FOR loop
Is the use of rowids worth the effort?
The LOB Datatypes
Working with LOBs
Understanding LOB Locators
Empty Versus NULL LOBs
Creating a LOB
Writing into a LOB
Reading from a LOB
BFILEs Are Different
Creating a BFILE locator
Accessing BFILEs
Using BFILEs to load LOB columns
Temporary LOBs
Creating a temporary LOB
Freeing a temporary LOB
Checking to see whether a LOB is temporary
Managing temporary LOBs
Native LOB Operations
SQL semantics may yield temporary LOBs
Performance impact of using SQL semantics
LOB Conversion Functions
Predefined Object Types
The XMLType Type
The URI Types
The Any Types
Part IV
DML and Transaction Management
DML in PL/SQL
A Quick Introduction to DML
The INSERT statement
The UPDATE statement
The DELETE statement
Cursor Attributes for DML Operations
RETURNING Information from DML Statements
DML and Exception Handling
DML and Records
Record-based inserts
Record-based updates
Using records with the RETURNING clause
Restrictions on record-based inserts and updates
Bulk DML with the FORALL Statement
Syntax of the FORALL Statement
Context-Switching Problem Scenarios
FORALL Examples
Cursor Attributes for FORALL
ROLLBACK Behavior with FORALL
Continuing Past Exceptions with FORALL
Driving FORALL with Nonsequential Arrays
INDICES OF example
VALUES OF example
Transaction Management
The COMMIT Statement
The ROLLBACK Statement
The SAVEPOINT Statement
The SET TRANSACTION Statement
The LOCK TABLE Statement
Autonomous Transactions
Defining Autonomous Transactions
Rules and Restrictions on Autonomous Transactions
Transaction Visibility
When to Use Autonomous Transactions
Building an Autonomous Logging Mechanism
Data Retrieval
Cursor Basics
Some Data Retrieval Terms
Typical Query Operations
Introduction to Cursor Attributes
The %FOUND attribute
The %NOTFOUND attribute
The %ROWCOUNT attribute
The %ISOPEN attribute
The %BULK_ROWCOUNT attribute
The %BULK_EXCEPTIONS attribute
Referencing PL/SQL Variables in a Cursor
Identifier precedence in a cursor
Using standard naming conventions
Choosing Between Explicit and Implicit Cursors
Working with Implicit Cursors
Implicit Cursor Examples
Error Handling with Implicit Cursors
Implicit SQL Cursor Attributes
Working with Explicit Cursors
Declaring Explicit Cursors
Naming your cursor
Declaring cursors in packages
Opening Explicit Cursors
Fetching from Explicit Cursors
Examples of explicit cursors
Fetching past the last row
Column Aliases in Explicit Cursors
Closing Explicit Cursors
Explicit Cursor Attributes
Cursor Parameters
Generalizing cursors with parameters
Opening cursors with parameters
Scope of cursor parameters
Cursor parameter modes
Default values for parameters
BULK COLLECT
Limiting Rows Retrieved with BULK COLLECT
Bulk Fetching of Multiple Columns
Using the RETURNING Clause with Bulk Operations
SELECT . . . FOR UPDATE
Releasing Locks with COMMIT
The WHERE CURRENT OF Clause
Cursor Variables and REF CURSORs
Why Cursor Variables?
Similarities to Static Cursors
Declaring REF CURSOR Types
Declaring Cursor Variables
Opening Cursor Variables
Fetching from Cursor Variables
Handling the ROWTYPE_MISMATCH exception
Rules for Cursor Variables
Compile-time rowtype matching rules
Runtime rowtype matching rules
Cursor variable aliases
Scope of cursor object
Passing Cursor Variables as Arguments
Identifying the REF CURSOR type
Setting the parameter mode
Cursor Variable Restrictions
Cursor Expressions
Using Cursor Expressions
Retrieve subquery as column
Implement a streaming function with the CURSOR expression
Restrictions on Cursor Expressions
Dynamic SQL and Dynamic PL/SQL
NDS Statements
The EXECUTE IMMEDIATE Statement
The OPEN FOR Statement
FETCH into variables or records
The USING clause in OPEN FOR
About the Four Dynamic SQL Methods
Method 1
Method 2
Method 3
Method 4
Binding Variables
Argument Modes
Duplicate Placeholders
Passing NULL Values
Working with Objects and Collections
Dynamic PL/SQL
Replace Repetitive Code with Dynamic Block
Implement Method 4 Dynamic SQL in NDS
Recommendations for NDS
Use Invoker Rights for Shared Programs
Anticipate and Handle Dynamic Errors
Use Binding Rather Than Concatenation
Avoid Code Injection with Binding
When to Use DBMS_SQL
Parse Very Long Strings
Obtain Information About Query Columns
Meet Method 4 Dynamic SQL Requirements
The "in table" procedural interface
Steps for intab construction
Constructing the SELECT
Defining the cursor structure
Retrieving and displaying data
Minimize Parsing of Dynamic Cursors
NDS Utility Package
Part V
Procedures, Functions, and Parameters
Modular Code
Procedures
Calling a Procedure
The Procedure Header
The Procedure Body
The END Descriptor
The RETURN Statement
Functions
Structure of a Function
The RETURN Datatype
The END Descriptor
Calling a Function
Functions Without Parameters
The Function Header
The Function Body
The RETURN Statement
RETURN any valid expression
Multiple RETURNs
RETURN as last executable statement
Parameters
Defining Parameters
Actual and Formal Parameters
Parameter Modes
IN mode
OUT mode
IN OUT mode
Matching Actual and Formal Parameters in PL/SQL
Positional notation
Named notation
Benefits of named notation
The NOCOPY Parameter Mode Hint
Restrictions on NOCOPY
Impact of NOCOPY
Default Values
Local Modules
Benefits of Local Modularization
Reducing code volume
Improving readability
Scope of Local Modules
Sprucing Up Your Code with Local Modules
Module Overloading
Benefits of Overloading
Supporting many data combinations
Restrictions on Overloading
Overloading with Numeric Types
Forward Declarations
Advanced Topics
Calling Your Function Inside SQL
Requirements for calling functions in SQL
Restrictions on user-defined functions in SQL
Read consistency and user-defined functions
Replacing DECODEs with IF statements
The PRAGMA RESTRICT_REFERENCES (Oracle8 Database and earlier)
Table Functions
Calling a function in a FROM clause
Passing table function results with a cursor variable
Creating a streaming function
Creating a pipelined function
Enabling a function for parallel execution
Deterministic Functions
Go Forth and Modularize!
Packages
Why Packages?
Demonstrating the Power of the Package
Some Package-Related Concepts
Diagramming Privacy
Rules for Building Packages
The Package Specification
The Package Body
Initializing Packages
Execute complex initialization logic
Cache static session information
Avoid side effects when initializing
When initialization fails
Rules for Calling Packaged Elements
Working with Package Data
Global Within a Single Oracle Session
Global Public Data
Packaged Cursors
Declaring packaged cursors
Working with packaged cursors
Serializable Packages
When to Use Packages
Encapsulating Data Manipulation
Avoid Hardcoding Literals
Improve Usability of Built-in Features
Group Together Logically Related Functionality
Cache Static Session Data
Packages and Object Types
Triggers
DML Triggers
DML Trigger Concepts
DML trigger scripts
Transaction participation
Creating a DML Trigger
The WHEN clause
Working with NEW and OLD pseudo-records
Determining the DML action within a trigger
DML Trigger Example: No Cheating Allowed!
Applying the WHEN clause
Using pseudo-records to fine-tune trigger execution
Multiple Triggers of the Same Type
Mutating Table Errors
DDL Triggers
Creating a DDL Trigger
Available Events
Available Attributes
Working with Events and Attributes
What column did I touch?
Lists returned by attribute functions
Dropping the Undroppable
The INSTEAD OF CREATE Trigger
Database Event Triggers
Creating a Database Event Trigger
The STARTUP Trigger
The SHUTDOWN Trigger
The LOGON Trigger
The LOGOFF Trigger
The SERVERERROR Trigger
SERVERERROR examples
Central error handler
INSTEAD OF Triggers
Creating an INSTEAD OF Trigger
The INSTEAD OF INSERT Trigger
The INSTEAD OF UPDATE Trigger
The INSTEAD OF DELETE Trigger
Populating the Tables
INSTEAD OF Triggers on Nested Tables
AFTER SUSPEND Triggers
Setting Up for the AFTER SUSPEND Trigger
Looking at the Actual Trigger
The ORA_SPACE_ERROR_INFO Function
The DBMS_RESUMABLE Package
Trapped Multiple Times
To Fix or Not to Fix?
Maintaining Triggers
Disabling, Enabling, and Dropping Triggers
Viewing Triggers
Checking the Validity of Triggers
Managing PL/SQL Code
Managing Code in the Database
Data Dictionary Views for PL/SQL Programmers
Display information about stored objects
Display and search source code
Use program size to determine pinning requirements
Obtain properties of stored code
Analyze and modify trigger state through views
Analyze argument information
Recompiling Invalid Code
Recompile individual program units
Use UTL_RECOMP
Using Native Compilation
Perform One-Time DBA Setup
Step 1: Get a Supported C Compiler
Step 2: Set Up the Directories
Step 3: Check $ORACLE_HOME/plsql/spnc_commands
Interpreted Versus Native Compilation Mode
Using the Optimizing Compiler and Compile Time Warnings
The Optimizing Compiler
Compile-Time Warnings
A quick example
If you see a "no message file" message
Verify your SQL*Plus version
How to turn on compile-time warnings
Warnings Available in Oracle Database 10g
PLW-05000: mismatch in NOCOPY qualification between specification and body
PLW-05001: previous use of 'string' (at line string) conflicts with this use
PLW-05003: same actual parameter(string and string) at IN and NOCOPY may have side effects
PLW-05004: identifier string is also declared in STANDARD or is a SQL built-in
PLW-05005: function string returns without value at line string
PLW-06002: unreachable code
PLW-07203: parameter 'string' may benefit from use of the NOCOPY compiler hint
PLW-07204: conversion away from column type may result in sub-optimal query plan
Conditional Compilation
Examples of Conditional Compilation
Use application package constants in $IF directive
Toggle tracing through conditional compilation flags
The Inquiry Directive
The DBMS_DB_VERSION package
Setting compilation environment parameters
Referencing unit name and line number
Using the PLSQL_CCFLAGS parameter
The $IF Directive
The $ERROR Directive
Synchronizing Code with Packaged Constants
Program-Specific Settings with Inquiry Directives
Working with Postprocessed Code
Testing PL/SQL Programs
Typical, Tawdry Testing Techniques
utPLSQL: A Unit-Testing Framework
Using utPLSQL with betwnstr
Where to find utPLSQL and Ounit
Debugging PL/SQL Programs
The Wrong Way to Debug
Disorganized debugging
Irrational debugging
Debugging Tips and Strategies
Use a source code debugger
Gather data
Remain logical at all times
Analyze instead of trying
Take breaks, and ask for help
Change and test one area of code at a time
Tracing Execution of Your Code
DBMS_UTILITY.FORMAT_CALL_STACK
Installing DBMS_TRACE
DBMS_TRACE programs
Controlling trace file contents
Pausing and resuming the trace process
Format of collected data
Tuning PL/SQL Programs
Analyzing Performance of PL/SQL Code
Optimizing PL/SQL Performance
Use the most aggressive compiler optimization level possible
Use BULK COLLECT when querying multiple rows
Use FORALL when modifying multiple rows
Use the NOCOPY hint when passing large structures
Use PLS_INTEGER for intensive integer computations.
Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic
Group together related programs in a package
Pin into shared memory large and frequently executed programs.
Protecting Stored Code
Restrictions on and Limitations of Wrapping
Using the Wrap Executable
Dynamic Wrapping with DBMS_DDL
Guidelines for Working with Wrapped Code
I/O and PL/SQL
Displaying Information
Enabling DBMS_OUTPUT
Write Lines to a Buffer
Read the Contents of a Buffer
Reading and Writing Files
The UTL_FILE_DIR Parameter
Setting up directories
Specifying file locations
Work with Oracle Directories
Open Files
Is the File Already Open?
Close Files
Read from Files
GET_LINE exceptions
Handy encapsulation for GET_LINE
Write to Files
Writing formatted text to file
Copy Files
Delete Files
Rename and Move Files
Retrieve File Attributes
Sending Email
Oracle Prerequisites by Release
Send a Short (32K or Less) Plaintext Message
Include "Friendly" Names in Email Addresses
Send a Plaintext Message of Arbitrary Length
Send a Message with a Short (& 32K) Attachment
Send a Small File (& 32K) as an Attachment
Attach a File of Arbitrary Size
Working with Web-Based Data (HTTP)
Retrieve a Web Page in "Pieces"
Retrieve a Web Page into a LOB
Authenticate Using HTTP Username/Password
Retrieve an SSL-Encrypted Web Page (Via https)
Submit Data to a Web Page via GET or POST
Disable Cookies or Make Cookies Persistent
Retrieve Data from an FTP Server
Use a Proxy Server
Other Types of I/O Available in PL/SQL
Database Pipes, Queues, and Alerts
TCP Sockets
Oracle's Built-in Web Server
Part VI
Application Security and PL/SQL
Security Overview
Encryption
Key Length
Algorithms
Padding and Chaining
The DBMS_CRYPTO Package
Algorithms
Padding and chaining
Encrypting Data
Encrypting LOBs
Decrypting Data
Performing Key Generation
Performing Key Management
A single key for the database
A single key for each row
A combined approach
Cryptographic Hashing
Using Message Authentication Code
Using Transparent Data Encryption (TDE)
Row-Level Security
Why Learn About RLS?
A Simple RLS Example
Using Dynamic Policies
Shared static policy
Context-sensitive policy
Shared context sensitive policy
Using Column-Sensitive RLS
RLS Debugging
Interpreting errors
Performing direct path operations
Viewing SQL statements
Application Contexts
Using Application Contexts
Security in Contexts
Contexts as Predicates in RLS
Identifying Non-Database Users
Fine-Grained Auditing
Why Learn About FGA?
A Simple FGA Example
Access How Many Columns?
Checking the Audit Trail
Using Bind Variables
Using Handler Modules
Inside PL/SQL
Looking Under the Hood
PL/SQL Concepts
Physical Storage of Server-Side PL/SQL
Compiler Limits
PL/SQL's Optimizing Compiler
Runtime Optimization of Fetch Loops
Dependency Management
Dependencies in Server-Side PL/SQL
Healing Invalids
Recompiling by hand
Recompiling by script
Automatic recompilation
Remote Dependencies
Execution Authority Models
The Definer Rights Model
Advantages of definer rights
Disadvantages of definer rights
The Invoker Rights Model
Invoker rights syntax
Some rules and restrictions
Combining Rights Models
PL/SQL and Oracle Memory
PGA, UGA, and CGA
Cursors, Memory, and More
Tips on Reducing Memory Use
Statement sharing
Bind variables
Packaging to improve memory use and performance
Large collections in PL/SQL
Preservation of state
What to Do if You Run Out of Memory
Server-Side PL/SQL Processing: Reprise
Compiling an Anonymous Block
Compiling a Stored Object
Executing PL/SQL
What You Need to Know
Globalization and Localization in PL/SQL
Overview and Terminology
Unicode Primer
National Character Set Datatypes
Character Encoding
Globalization Support Parameters
Unicode Functions
ASCIISTR
COMPOSE
DECOMPOSE
INSTR/INSTRB/INSTRC/INSTR2/INSTR4
LENGTH/LENGTHB/LENGTHC/LENGTH2/LENGTH4
SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4
UNISTR
Character Semantics
String Sort Order
Binary Sort
Monolingual Sort
Multilingual Sort
Multilingual Information Retrieval
IR and PL/SQL
Date/Time
Timestamp Datatypes
Date/Time Formatting
Currency Conversion
Globalization Development Kit for PL/SQL
UTL_118N Utility Package
UTL_LMS Error-Handling Package
GDK Implementation Options
Method 1: Locale buttons
Method 2: User administration
Method 3: Hybrid
Object-Oriented Aspects of PL/SQL
Introduction to Oracle's Object Features
An Extended Example
A Tree of Types
Creating a base type
Creating a subtype
Methods
Storing, Retrieving, and Using Persistent Objects
Object identity
The VALUE function
The TREAT function
Evolution and Creation
Back to Pointers?
Using REFs
The UTL_REF package
REFs and type hierarchies
Dangling REFs
Generic Data: The ANY Types
Preview: What ANYDATA is not
Dealing with an ANYDATA
Creating a transient type
I Can Do It Myself
Comparing Objects
Attribute-level comparison
The MAP method
The ORDER method
Additional comparison recommendations
Object Views
A Sample Relational System
Object View with a Collection Attribute
Object Subview
Object View with Inverse Relationship
INSTEAD OF Triggers
The case against
The case for
The bigger question
Differences Between Object Views and Object Tables
OID uniqueness
"Storeability" of physical versus virtual REFs
REFs to nonunique OIDs
Maintaining Object Types and Object Views
Privileges
The EXECUTE privilege
The UNDER privilege
The DEBUG privilege
The DML privileges
Pontifications
Calling Java from PL/SQL
Oracle and Java
Getting Ready to Use Java in Oracle
Installing Java
Building and Compiling Your Java Code
Setting Permissions for Java Development and Execution
Java security for Oracle through 8.1.5
Java security for Oracle for 8.1.6 through Oracle Database 10g
A Simple Demonstration
Finding the Java Functionality
Building a Custom Java Class
Compiling and Loading into Oracle
Building a PL/SQL Wrapper
Deleting Files from PL/SQL
Using loadjava
Using dropjava
Managing Java in the Database
The Java Namespace in Oracle
Examining Loaded Java Elements
Using DBMS_JAVA
LONGNAME: Converting Java Long Names
GET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting (a Few) Compiler Options
SET_OUTPUT: Enabling Output from Java
EXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects
Publishing and Using Java in PL/SQL
Call Specs
Some Rules for Call Specs
Mapping Datatypes
Calling a Java Method in SQL
Exception Handling with Java
Extending File I/O Capabilities
Polishing up the delete method
Obtaining directory contents
Other Examples
External Procedures
Introduction to External Procedures
Example: Invoking an Operating System Command
Architecture of External Procedures
The Oracle Net Configuration
Specifying the Listener Configuration
Security Characteristics of the Configuration
Setting Up Multithreaded Mode
Creating an Oracle Library
Writing the Call Specification
The Call Spec: Overall Syntax
Parameter Mapping: The Example Revisited
Parameter Mapping: The Full Story
More Syntax: The PARAMETERS Clause
PARAMETERS Properties
The INDICATOR property
The LENGTH property
The MAXLEN property
The CHARSETID and CHARSETFORM properties
Raising an Exception from the Called C Program
Nondefault Agents
Maintaining External Procedures
Dropping Libraries
Data Dictionary
Rules and Warnings
Regular Expression Metacharacters and Function Parameters
Metacharacters
Function Parameters
Number Format Models
Date Format Models
Index

Systemvoraussetzungen

Als PDF speichern Als Link merken