Sign in  |  Register
Advanced search
Publication Cover

CITATION

McLaughlin, Michael. Oracle Database 12c PL/SQL Programming. McGraw-Hill Osborne Media, 2014.

Oracle Database 12c PL/SQL Programming

Authors: Michael McLaughlin

Published:  February 2014

eISBN: 9780071812443 007181244X | ISBN: 9780071812436
Open eBook
  • Cover
  • Title Page
  • Copyright Page
  • About the Author
  • About the Contributing Author
  • About the Technical Editor
  • Contents at a Glance
  • Contents
  • Acknowledgments
  • Introduction
  • Book Outline
  • Part I: PL/SQL Fundamentals
  • Part II: PL/SQL Programming
  • Part III: Appendixes and Glossary
  • Lexicon
  • SQL Lexicon
  • PL/SQL Stored Programs
  • Other Conventions
  • Data Model and Source Code to Download
  • Part I: Oracle PL/SQL
  • Chapter 1: Oracle PL/SQL Development Overview
  • PL/SQL’s History and Background
  • Oracle Development Architecture
  • The Database
  • The PL/SQL Language
  • The Oracle Processing Architecture
  • Two-Tier Model
  • N-Tier Model
  • Summary
  • Mastery Check
  • Chapter 2: New Features
  • New SQL Features
  • Data Catalog DIRECTORY Qualifies a LIBRARY Object
  • Define Tables with Valid-Time (VT) Support
  • Enhanced Oracle Native LEFT OUTER JOIN Syntax
  • Default Values for Columns Based on Sequences
  • Default Values for Explicit Null Insertion
  • Identity Columns
  • Increased Size Limits of String and Raw Types
  • Pass Results from SQL Statements to External Programs
  • Native SQL Support for Query Row Limits and Offsets
  • Oracle Database Driver for MySQL Applications
  • SQL CROSS APPLY, OUTER APPLY, and LATERAL
  • Bequeath CURRENT_USER Views
  • New PL/SQL Features
  • Caching of Invoker Rights Functions
  • Ability to White List PL/SQL Program Unit Callers
  • Native Client API Support for PL/SQL Types
  • New PL/SQL Package UTL_CALL_STACK
  • DBMS_UTILITY Adds EXPAND_SQL_TEXT Subprogram
  • DBMS_SQL Adds a New Formal Schema to the PARSE Procedure
  • PL/SQL Functions in SQL WITH Clause
  • PL/SQL-Specific Data Types Allowed in SQL
  • Implicit REF CURSOR Parameter Binding
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Chapter 3: PL/SQL Basics
  • Block Structure
  • Execution Block
  • Basic Block Structure
  • Declaration Block
  • Exception Block
  • Behavior of Variables in Blocks
  • Anonymous Blocks
  • Nested Anonymous Blocks
  • Local Named Blocks
  • Stored Named Blocks
  • Basic Scalar and Composite Data Types
  • Scalar Data Types
  • Attribute and Table Anchoring
  • Composite Data Types
  • Control Structures
  • Conditional Structures
  • Iterative Structures
  • Exceptions
  • User-Defined Exceptions
  • Dynamic User-Defined Exceptions
  • Bulk Operations
  • Functions, Procedures, and Packages
  • Functions
  • Procedures
  • Packages
  • Transaction Scope
  • Single Transaction Scope
  • Multiple Transaction Scopes
  • Database Triggers
  • Summary
  • Mastery Check
  • Chapter 4: Language Fundamentals
  • Lexical Units
  • Delimiters
  • Identifiers
  • Literals
  • Comments
  • Variables and Data Types
  • Variable Data Types
  • Scalar Data Types
  • Large Objects (LOBs)
  • Composite Data Types
  • System Reference Cursors
  • Summary
  • Mastery Check
  • Chapter 5: Control Structures
  • Conditional Statements
  • IF Statements
  • CASE Statements
  • Conditional Compilation Statements
  • Iterative Statements
  • Simple Loop Statements
  • FOR Loop Statements
  • WHILE Loop Statements
  • Cursor Structures
  • Implicit Cursors
  • Explicit Cursors
  • Bulk Statements
  • BULK COLLECT INTO Statements
  • FORALL Statements
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Chapter 6: Collections
  • Introduction to Collections
  • Object Types: Varray and Table Collections
  • Varray Collections
  • Table Collections
  • Associative Arrays
  • Defining and Using Associative Arrays
  • Oracle Collection API
  • COUNT Method
  • DELETE Method
  • EXISTS Method
  • EXTEND Method
  • FIRST Method
  • LAST Method
  • LIMIT Method
  • NEXT Method
  • PRIOR Method
  • TRIM Method
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Chapter 7: Error Management
  • Exception Types and Scope
  • Compilation Errors
  • Runtime Errors
  • Exception Management Built-in Functions
  • User-Defined Exceptions
  • Declaring User-Defined Exceptions
  • Dynamic User-Defined Exceptions
  • Exception Stack Functions
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Part II: PL/SQL Programming
  • Chapter 8: Functions and Procedures
  • Function and Procedure Architecture
  • Transaction Scope
  • Calling Subroutines
  • Positional Notation
  • Named Notation
  • Mixed Notation
  • Exclusionary Notation
  • SQL Call Notation
  • Functions
  • Function Model Choices
  • Creation Options
  • Pass-by-Value Functions
  • Pass-by-Reference Functions
  • Procedures
  • Pass-by-Value Procedures
  • Pass-by-Reference Procedures
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Chapter 9: Packages
  • Package Architecture
  • Package Specification
  • Prototype Features
  • Serially Reusable Precompiler Directive
  • Variables
  • Types
  • Components: Functions and Procedures
  • Package Body
  • Prototype Features
  • Variables
  • Types
  • Components: Functions and Procedures
  • Definer vs. Invoker Rights Mechanics
  • Managing Packages in the Database Catalog
  • Finding, Validating, and Describing Packages
  • Checking Dependencies
  • Comparing Validation Methods: Timestamp vs. Signature
  • Summary
  • Mastery Check
  • Chapter 10: Large Objects
  • Working with Internally Stored LOB Types
  • LOB Assignments Under 32K
  • LOB Assignments over 32K
  • Reading Files into Internally Stored Columns
  • Reading Local Files into CLOB or NCLOB Columns
  • Reading Local Files into BLOB Columns
  • Working with LOBs Through Web Pages
  • Working with Binary Files (BFILEs)
  • Creating and Using Virtual Directories
  • Reading Canonical Path Names and Filenames
  • Understanding the DBMS_LOB Package
  • Package Constants
  • Package Exceptions
  • Opening and Closing Methods
  • Manipulation Methods
  • Introspection Methods
  • BFILE Methods
  • Temporary LOB Methods
  • Security Link Methods
  • Supporting Scripts
  • The LONG to CLOB Script
  • Manage LOBs from the File System
  • Manage CLOB and BLOB LOBs Through the Web
  • Manage BFILE LOBs Through the Web
  • Summary
  • Mastery Check
  • Chapter 11: Object Types
  • Object Basics
  • Declaring Objects Types
  • Implementing Object Bodies
  • White Listing Object Types
  • Getters and Setters
  • Static Member Methods
  • Comparing Objects
  • Inheritance and Polymorphism
  • Declaring Subclasses
  • Implementing Subclasses
  • Type Evolution
  • Implementing Object Type Collections
  • Declaring Object Type Collections
  • Implementing Object Type Collections
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Chapter 12: Triggers
  • Introduction to Triggers
  • Database Trigger Architecture
  • Data Definition Language Triggers
  • Event Attribute Functions
  • Building DDL Triggers
  • Data Manipulation Language Triggers
  • Statement-Level Triggers
  • Row-Level Triggers
  • Compound Triggers
  • INSTEAD OF Triggers
  • System and Database Event Triggers
  • Trigger Restrictions
  • Maximum Trigger Size
  • SQL Statements
  • LONG and LONG RAW Data Types
  • Mutating Tables
  • System Triggers
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Chapter 13: Dynamic SQL
  • Dynamic SQL Architecture
  • Native Dynamic SQL (NDS)
  • Dynamic Statements
  • Dynamic Statements with Inputs
  • Dynamic Statements with Inputs and Outputs
  • Dynamic Statements with an Unknown Number of Inputs
  • DBMS_SQL Package
  • Dynamic Statements
  • Dynamic Statements with Input Variables
  • Dynamic Statements with Variable Inputs and Fixed Outputs
  • Dynamic Statements with Variable Inputs and Outputs
  • DBMS_SQL Package Definition
  • Supporting Scripts
  • Summary
  • Mastery Check
  • Part III: Appendixes and Glossary
  • Appendix A: Oracle Database Primer
  • Oracle Database Architecture
  • Starting and Stopping the Oracle Database 12c Server
  • Unix or Linux Operations
  • Microsoft Windows Operations
  • Starting and Stopping the Oracle Listener
  • Multiversion Concurrency Control
  • Data Transactions
  • DML Locking and Isolation Control
  • Definer Rights and Invoker Rights
  • Definer Rights
  • Invoker Rights
  • SQL Interactive and Batch Processing
  • SQL*Plus Command-Line Interface
  • Oracle SQL Developer Interface
  • Database Administration
  • Provisioning Users
  • Using Database Constraints
  • Security Hardening
  • Data Governance
  • SQL Tuning
  • EXPLAIN PLAN Statement
  • DBMS_XPLAN Package
  • SQL Tracing
  • Tracing Session Statements
  • Convert Raw Trace Files to Readable Trace Files
  • Summary
  • Appendix B: SQL Primer
  • Oracle SQL Data Types
  • Data Definition Language (DDL)
  • CREATE Statement
  • ALTER Statement
  • RENAME Statement
  • DROP Statement
  • TRUNCATE Statement
  • COMMENT Statement
  • Data Manipulation Language (DML)
  • ACID Compliant Transactions
  • INSERT Statement
  • UPDATE Statement
  • DELETE Statement
  • MERGE Statement
  • Transaction Control Language (TCL)
  • Queries: SELECT Statements
  • Queries that Return Columns or Results from Columns
  • Queries that Aggregate
  • Queries that Return Columns or Results Selectively
  • Join Results
  • Joins that Splice Together Rows
  • Joins that Splice Collections
  • Summary
  • Appendix C: SQL Built-in Functions
  • Character Functions
  • ASCII Function
  • ASCIISTR Function
  • CHR Function
  • CONCAT Function
  • INITCAP Function
  • INSTR Function
  • LENGTH Function
  • LOWER Function
  • LPAD Function
  • LTRIM Function
  • REPLACE Function
  • REVERSE Function
  • RPAD Function
  • RTRIM Function
  • UPPER Function
  • Data Type Conversion Functions
  • CAST Function
  • CONVERT Function
  • TO_CHAR Function
  • TO_CLOB Function
  • TO_DATE Function
  • TO_LOB Function
  • TO_NCHAR Function
  • TO_NCLOB Function
  • TO_NUMBER Function
  • Date-time Conversion Functions
  • ADD_MONTHS Function
  • CURRENT_DATE Function
  • CURRENT_TIMESTAMP Function
  • DBTIMEZONE Function
  • EXTRACT Function
  • FROM_TZ Function
  • LAST_DAY Function
  • LOCALTIMESTAMP Function
  • MONTHS_BETWEEN Function
  • NEW_TIME Function
  • ROUND Function
  • SYSDATE Function
  • SYSTIMESTAMP Function
  • TO_CHAR(date) Function
  • TO_DSINTERVAL Function
  • TO_TIMESTAMP Function
  • TO_TIMESTAMP_TZ Function
  • TO_YMINTERVAL Function
  • TRUNC(date) Function
  • TZ_OFFSET Function
  • Collection Management Functions
  • CARDINALITY Function
  • COLLECT Function
  • POWERMULTISET Function
  • POWERMULTISET_BY_CARDINALITY Function
  • SET Function
  • Collection Set Operators
  • CARDINALITY Operator
  • EMPTY Operator
  • MULTISET Operator
  • MULTISET EXCEPT Operator
  • MULTISET INTERSECT Operator
  • MULTISET UNION Operator
  • SET Operator
  • SUBMULTISET OF Operator
  • Number Functions
  • CEIL Function
  • FLOOR Function
  • MOD Function
  • POWER Function
  • REMAINDER Function
  • ROUND Function
  • Error Reporting Functions
  • SQLCODE Function
  • SQLERRM Function
  • Miscellaneous Functions
  • BFILENAME Function
  • COALESCE Function
  • DECODE Function
  • DUMP Function
  • EMPTY_BLOB Function
  • EMPTY_CLOB Function
  • GREATEST Function
  • LEAST Function
  • NANVL Function
  • NULLIF Function
  • NVL Function
  • SYS_CONTEXT Function
  • TABLE Function
  • TREAT Function
  • USERENV Function
  • VSIZE Function
  • Summary
  • Appendix D: PL/SQL Built-in Packages and Types
  • Oracle Database 11g and 12c New Packages
  • Examples of Package Use
  • DBMS_APPLICATION_INFO Example
  • DBMS_COMPARISON
  • DBMS_CRYPTO
  • DBMS_FGA
  • Case Study: Query Tool
  • Supporting Scripts
  • Summary
  • Appendix E: Regular Expression Primer
  • Regular Expression Introduction
  • Character Classes
  • Collation Classes
  • Metacharacters
  • Metasequences
  • Literals
  • Regular Expression Implementation
  • REGEXP_COUNT Function
  • REGEXP_INSTR Function
  • REGEXP_LIKE Function
  • REGEXP_REPLACE Function
  • REGEXP_SUBSTR Function
  • Supporting Scripts
  • Summary
  • Appendix F: Wrapping PL/SQL Code Primer
  • Limitations of Wrapping PL/SQL
  • Limitations of the PL/SQL wrap Utility
  • Limitations of the DBMS_DDL.WRAP Function
  • Using the wrap Command-Line Utility
  • Using the DBMS_DDL Command-Line Utility
  • WRAP Function
  • CREATE_WRAPPED Procedure
  • Summary
  • Appendix G: PL/SQL Hierarchical Profiler Primer
  • Configuring the Schema
  • Collecting Profiler Data
  • Understanding Profiler Data
  • Reading the Raw Output
  • Defining the PL/SQL Profiler Tables
  • Querying the Analyzed Data
  • Using the plshprof Command-Line Utility
  • Supporting Scripts
  • Summary
  • Appendix H: PL/SQL Reserved Words and Keywords
  • Summary
  • Appendix I: Mastery Check Answers
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Glossary
  • Index