Oracle Database 12c PL/SQL Advanced Programming Techniques

Oracle Database 12c PL/SQL Advanced Programming Techniques

by Michael McLaughlin, John Harper
Released November 2014
Publisher(s): McGraw Hill Computing
ISBN: 9780071835152

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

Take Your PL/SQL Programming Skills to the Next Level

Build robust database-centric PL/SQL applications quickly and effectively. Oracle Database 12c PL/SQL Advanced Programming Techniques shows you how to write and deploy Java libraries inside Oracle Database 12c, use the utl_file and DBMS_SCHEDULER packages, and create external tables and external procedures. Application security, performance tuning, and Oracle Database In-Memory are also covered in this Oracle Press guide.

  • Configure, deploy, and troubleshoot Java libraries for Oracle object types
  • Use the utl_file package to manage unstructured and structured data
  • Develop and deploy Java I/O libraries and wrap them with PL/SQL
  • Create and use external tables
  • Implement high-speed data transfer
  • Harden database systems and develop secure applications
  • Manage complex schedules and jobs with the DBMS_SCHEDULER package
  • Optimize PL/SQL for use in performance tuning
  • Create and deploy external procedures
  • Implement the Oracle Database In-Memory column store feature

Table of contents

  1. Cover
  2. Title Page
  3. Copyright Page
  4. Dedication
  5. About the Authors
  6. Contents at a Glance
  7. Contents
  8. Acknowledgments
  9. Introduction
  10. Part I Java in the Database
    1. 1 Java Functions and Procedures
      1. Overview
      2. Oracle Database 12c JVM Features
      3. Java Architecture in Oracle
        1. Java Execution Control
        2. Java Resource Storage
        3. Java Class Names
        4. Java Resolvers
        5. Java Security and Permissions
        6. Java Threading
      4. Oracle Java Connection Types
        1. The Client-Side Driver, or JDBC Thin Driver
        2. The Oracle Call Interface Driver, or Middle-Tier Thick Driver
        3. The Oracle Server-Side Internal Driver, or Server-Tier Thick Driver
      5. Importing Java Class Libraries
        1. Building a Java File
        2. Creating a Deterministic Java Library
        3. Creating a JDBC-Enabled Java Library
      6. Supporting Scripts
      7. Summary
    2. 2 Object Types
      1. Overview
      2. Creating a Java Class with SQLData
        1. Basic Java Class with SQLData
        2. Object Type Wrapper
      3. Creating a Java Superclass and Object Types
        1. Creating the Java Superclass
        2. Creating the Object Type
        3. Testing the Java Class File
      4. Creating a Java Subclass and Object Subtype
        1. Creating a Java Subclass
        2. Creating the Object Subtype
        3. Testing the Java Subclass
      5. Troubleshooting Java Classes
        1. Building, Loading, and Dropping Java Class Library Objects
        2. Using Java Class Library Objects
      6. Mapping Oracle Types
      7. Supporting Scripts
      8. Summary
  11. Part II File I/O
    1. 3 utl_file Package
      1. Overview
      2. Accessing External Files
        1. Setting the utl_file_dir Database Parameter
        2. Using Virtual Directories
      3. Reading External Files
        1. Reading Files with the utl_file Package
        2. Implementing Best Practices
      4. Copying, Moving, Renaming, and Removing External Files
        1. Copying External Files
        2. Renaming or Moving External Files
        3. Removing External Files
      5. Creating and Writing to External Files
        1. Writing Unstructured Data to External Files
        2. Writing Structured Data to External Files
      6. Supporting Scripts
      7. Summary
    2. 4 Java I/O Libraries
      1. Overview
      2. Mapping Virtual Directories to Physical Directories
        1. Creating a Virtual Directory
        2. Translating Virtual Names to Paths
        3. Extracting a File Path and Name
      3. Setting Java Privileges
        1. Reading from an External Directory
        2. Reading from an External File
        3. Writing to an External File
        4. Deleting from an External File
      4. Developing and Deploying Java I/O Libraries
        1. Reading a Directory
        2. Reading a File
        3. Copying a File
        4. Deleting a File
        5. Writing a File
      5. Supporting Scripts
      6. Summary
    3. 5 External Tables
      1. Overview
      2. Using External Tables
        1. Creating Virtual Directories and Granting Database Privileges
        2. Importing a Framework’s External Table
      3. Leveraging the Data Catalog
      4. Wrapping Access to External Tables
      5. Cleaning Up the External Table Files
      6. Supporting Scripts
      7. Summary
    4. 6 High-Speed Data Transfer
      1. Overview
      2. Importing and Exporting with impdp and expdp
        1. Exporting Files with expdp
      3. Importing Files with impdp
      4. Importing and Exporting with dbms_datapump
      5. SQL*Loader
      6. Supporting Scripts
      7. Summary
  12. Part III Application Security
    1. 7 Database Security
      1. Overview
      2. LinkedIn Breach
        1. Key Points of the LinkedIn Breach
        2. Solutions to Avoid a Similar Breach
      3. Target Breach
        1. Key Points of the Target Breach
        2. Suggested Remediation for the Breach
      4. NSA Breach
        1. Key Points of the NSA Breach
        2. Suggested Remediation for the Breach
      5. Utah Department of Technology Services Breach
        1. Key Points of the Utah DTS Breach
        2. Solutions to Avoid a Similar Breach
      6. NIST
      7. SANS
      8. Planning and Implementing Cybersecurity
      9. Separation of Duties
      10. Supporting Scripts
      11. Summary
    2. 8 Developing Secure Applications
      1. Overview
      2. Programming Patterns
      3. Auditing with Oracle Audit Vault
      4. Secure Application Roles
        1. Proxy Users
        2. The Database Security Schema
        3. The utl_sec Package
      5. Oracle’s New Multitenant Architecture
      6. Sanitizing SQL and PL/SQL
        1. Bind Variables
        2. dbms_assert
      7. Supporting Scripts
      8. Summary
  13. Part IV Applied Technologies
    1. 9 dbms_scheduler Package
      1. Overview
      2. Date Intervals
        1. Immediate Jobs
        2. Simple Intervals
        3. Built-in Intervals
        4. Combined Custom Intervals
      3. dbms_scheduler View
        1. *_scheduler_jobs
        2. *_scheduler_running_jobs
        3. *_scheduler_job_run_details
      4. Chained and Clustered Jobs
      5. Job Management and Security
      6. Supporting Scripts
      7. Summary
    2. 10 Optimizing Using PL/SQL
      1. Overview
      2. Current Optimization Platforms
      3. v$* Views for Performance Tuning
      4. Gathering Statistics
      5. Statistics-Gathering Package
        1. Analyzing a Data Model
      6. Current Optimization Platforms
      7. Supporting Scripts
      8. Summary
    3. 11 External Procedures
      1. Overview
      2. Introducing External Procedures
      3. 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
      4. Troubleshooting External Procedures
        1. Configuration of the Listener or Environment
        2. Configuration of the Shared Library or PL/SQL Library Wrapper
      5. Supporting Files
      6. Summary
    4. 12 In-Memory Column Store
      1. Overview
      2. In-Memory Setup and Configuration
        1. Database Parameter Settings
        2. Tablespace Settings
      3. In-Memory Options and Syntax
        1. Priority
        2. Distribution
        3. Compression
      4. Query Performance
        1. Compression Stats
        2. Queries
      5. DML Performance
        1. Test Setup
      6. Supporting Scripts
        1. Test Results
      7. Summary
  14. A Oracle Database Java Primer
    1. Java and JDBC Architecture
    2. Configuring the Oracle Java Environment
    3. Java Programming Language Primer
      1. Java Basics
      2. Java Assignment Operators
      3. Java Conditional and Iterative Structures
      4. Java Method Definitions
      5. Java try-catch Blocks
    4. Testing a Client-Side or Thin-Driver JDBC Connection
    5. Accessing Scalar Variables
    6. Writing and Accessing Large Objects
      1. Writing and Accessing a CLOB Column
      2. Accessing a BFILE Column
      3. Supporting Scripts
    7. Summary
  15. Index

Product information

  • Title: Oracle Database 12c PL/SQL Advanced Programming Techniques
  • Author(s): Michael McLaughlin, John Harper
  • Release date: November 2014
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071835152