Oracle and PL/SQL Recipes: A Problem-Solution Approach

Oracle and PL/SQL Recipes: A Problem-Solution Approach

by Josh Juneau, Matt Arena
Released December 2010
Publisher(s): Apress
ISBN: 9781430232070

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

Oracle PL/SQL Recipes is your go to book for PL/SQL programming solutions. It takes a task-oriented approach to PL/SQL programming that lets you quickly look up a specific task and see the pattern for a solution. Then it's as simple as modifying the pattern for your specific application and implementing it. And you're done and home for dinner.

Oracle PL/SQL Recipes is another in Apress' ongoing series of recipe books aimed at Oracle practitioners. The recipe format is ideal for the busy professional who just needs to get the job done.

  • Covers the most common PL/SQL programming problems

  • Presents solutions in ready-to-use format

  • Stays short and to-the-point

What you'll learn

  • Move business logic into the database

  • Implement robust exception-handling

  • Optimize and debug, taking advantage of Oracle SQL Developer

  • Integrate PL/SQL with web-based applications

  • Interface with Java code and dynamic languages in the database, and running externally

  • Run the UTPLSQL unit-testing framework

    • Who this book is for

    Oracle PL/SQL Recipes is database administrators and developers wanting to take advantage of Oracle Database's built-in support for procedural logic. Database administrators can use PL/SQL to automate administrative tasks, audit access to sensitive data, and more. Developers will be able to optimize processing, push business logic into the database layer, and interface with Java and web applications.

    Table of contents

    1. Title Page
    2. Contents at a Glance
    3. Contents
    4. About the Authors
    5. About the Technical Reviewer
    6. Acknowledgments
    7. Introduction
      1. The Recipe Approach
      2. Audience
      3. Example Code
    8. CHAPTER 1: PL/SQL Fundamentals
      1. 1-1. Creating a Block of Code
      2. 1-2. Executing a Block of Code in SQL*Plus
      3. 1-3. Storing Code in a Script
      4. 1-4. Executing a Stored Script
      5. 1-5. Accepting User Input from the Keyboard
      6. 1-6. Displaying Results in SQL*Plus
      7. 1-7. Commenting Your Code
      8. 1-8. Referencing a Block of Code
      9. 1-9. Referring to Variables from Nested Blocks
      10. 1-10. Ignoring Substitution Variables
      11. 1-11. Changing the Substitution Variable Character
      12. 1-12. Creating a Variable to Match a Database Column Type
    9. CHAPTER 2: Essential SQL
      1. 2-1. Retrieving a Single Row from the Database
      2. 2-2. Qualifying Column and Variable Names
      3. 2-3. Declaring Variable Types That Match Column Types
      4. 2-4. Returning Queried Data into a PL/SQL Record
      5. 2-5. Creating Your Own Records to Receive Query Results
      6. 2-6. Looping Through Rows from a Query
      7. 2-7. Obtaining Environment and Session Information
      8. 2-8. Formatting Query Results
      9. 2-9. Updating Rows Returned by a Query
      10. 2-10. Updating Rows Returned by a Cursor
      11. 2-11. Deleting Rows Returned by a Cursor
      12. 2-12. Performing a Transaction
      13. 2-13. Ensuring That Multiple Queries “See” the Same Data
      14. 2-14. Executing One Transaction from Within Another
      15. 2-15. Finding and Removing Duplicate Table Rows
    10. CHAPTER 3: Looping and Logic
      1. 3-1. Choosing When to Execute Code
      2. 3-2. Choosing Between Two Mutually Exclusive Conditions
      3. 3-3. Evaluating Multiple Mutually Exclusive Conditions
      4. 3-4. Driving from an Expression Having Multiple Outcomes
      5. 3-5. Looping Until a Specified Condition Is Met
      6. 3-6. Iterating Cursor Results Until All Rows Have Been Returned
      7. 3-7. Iterating Until a Condition Evaluates to FALSE
      8. 3-8. Bypassing the Current Loop Iteration
      9. 3-9. Iterating a Fixed Number of Times
      10. 3-10. Iterating Backward Through a Range
      11. 3-11. Iterating in Increments Other Than One
      12. 3-12. Stepping Through a Loop Based on Odd-Numbered Increments
      13. 3-13. Exiting an Outer Loop Prematurely
      14. 3-14. Jumping to a Designated Location in Code
    11. CHAPTER 4: Functions, Packages, and Procedures
      1. 4-1. Creating a Stored Function
      2. 4-2. Executing a Stored Function from a Query
      3. 4-3. Optimizing a Function That Will Always Return the Same Result for a Given Input
      4. 4-4. Creating a Stored Procedure
      5. 4-5. Executing a Stored Procedure
      6. 4-6. Creating Functions Within a Procedure or Code Block
      7. 4-7. Passing Parameters by Name
      8. 4-8. Setting Default Parameter Values
      9. 4-9. Collecting Related Routines into a Single Unit
      10. 4-10. Writing Initialization Code for a Package
      11. 4-11. Granting the Ability to Create and Execute Stored Programs
      12. 4-12. Executing Packaged Procedures and Functions
      13. 4-13. Creating a Public Name for a Stored Program
      14. 4-14. Executing Package Programs in Sequence
      15. 4-15. Implementing a Failure Flag
      16. 4-16. Forcing Data Access to Go Through Packages
      17. 4-17. Executing Stored Code Under Your Own Privilege Set
      18. 4-18. Accepting Multiple Parameter Sets in One Function
      19. 4-19. Listing the Functions, Procedures, and Packages in a Schema
      20. 4-20. Viewing Source Code for Stored Programs
    12. CHAPTER 5: Triggers
      1. 5-1. Automatically Generating Column Values
      2. 5-2. Keeping Related Values in Sync
      3. 5-3. Responding to an Update of a Specific Table Column
      4. 5-4. Making a View Updatable
      5. 5-5. Altering the Functionality of Applications
      6. 5-6. Validating Input Data
      7. 5-7. Scrubbing Input Data
      8. 5-8. Replacing a Column's Value
      9. 5-9. Triggering on a System Event
      10. 5-10. Triggering on a Schema-Related Event
      11. 5-11. Firing Two Triggers on the Same Event
      12. 5-12. Creating a Trigger That Fires on Multiple Events
      13. 5-13. Creating a Trigger in a Disabled State
    13. CHAPTER 6: Type Conversion
      1. 6-1. Converting a String to a Number
      2. 6-2. Converting a String to a Date
      3. 6-3. Converting a Number to a String
      4. 6-4. Converting a Date to a String
      5. 6-5. Converting Strings to Timestamps
      6. 6-6. Writing ANSI-Compliant Conversions
      7. 6-7. Implicitly Converting Between PLS_INTEGER and NUMBER
    14. CHAPTER 7: Numbers, Strings, and Dates
      1. 7-1. Concatenating Strings
      2. 7-2. Adding Some Number of Days to a Date
      3. 7-3. Adding a Number of Months to a Date
      4. 7-4. Adding Years to a Date
      5. 7-5. Determining the Interval Between Two Dates
      6. 7-6. Adding Hours, Minutes, Seconds, or Days to a Given Date
      7. 7-7. Returning the First Day of a Given Month
      8. 7-8. Returning the Last Day of a Given Month
      9. 7-9. Rounding a Number
      10. 7-10. Rounding a Datetime Value
      11. 7-11. Tracking Time to a Millisecond
      12. 7-12. Associating a Time Zone with a Date and Time
      13. 7-13. Finding a Pattern Within a String
      14. 7-14. Determining the Position of a Pattern Within a String
      15. 7-15. Finding and Replacing Text Within a String
    15. CHAPTER 8: Dynamic SQL
      1. 8-1. Executing a Single Row Query That Is Unknown at Compile Time
      2. 8-2. Executing a Multiple Row Query That Is Unknown at Compile Time
      3. 8-3. Writing a Dynamic INSERT Statement
      4. 8-4. Writing a Dynamic Update Statement
      5. 8-5. Writing a Dynamic Delete Statement
      6. 8-6. Returning Data from a Dynamic Query into a Record
      7. 8-7. Executing a Dynamic Block of PL/SQL
      8. 8-8. Creating a Table at Runtime
      9. 8-9. Altering a Table at Runtime
      10. 8-10. Finding All Tables That Include a Specific Column Value
      11. 8-11 Storing Dynamic SQL in Large Objects
      12. 8-12. Passing NULL Values to Dynamic SQL
      13. 8-13. Switching Between DBMS_SQL and Native Dynamic SQL
      14. 8-14. Guarding Against SQL Injection Attacks
    16. CHAPTER 9: Exceptions
      1. 9-1. Trapping an Exception
      2. 9-2. Catching Unknown Exceptions
      3. 9-3. Creating and Raising Named Programmer-Defined Exceptions
      4. 9-4. Determining Which Error Occurred Inside the OTHERS Handler
      5. 9-5. Raising User-Defined Exceptions Without an Exception Handler
      6. 9-6. Redirecting Control After an Exception Is Raised
      7. 9-7. Raising Exceptions and Continuing Processing
      8. 9-8. Associating Error Numbers with Exceptions That Have No Name
      9. 9-9. Tracing an Exception to Its Origin
      10. 9-10. Displaying PL/SQL Compiler Warnings
    17. CHAPTER 10: PL/SQL Collections and Records
      1. 10-1. Creating and Accessing a VARRAY
      2. 10-2. Creating and Accessing an Indexed Table
      3. 10-3. Creating Simple Records
      4. 10-4. Creating and Accessing Record Collections
      5. 10-5. Creating and Accessing Hash Array Collections
      6. 10-6. Creating and Accessing Complex Collections
      7. 10-7. Passing a Collection As a Parameter
      8. 10-8. Returning a Collection As a Parameter
      9. 10-9. Counting the Members in a Collection
      10. 10-10. Deleting a Record from a Collection
      11. 10-11. Checking Whether an Element Exists
      12. 10-12. Increasing the Size of a Collection
      13. 10-13. Navigating Collections
      14. 10-14. Trimming a Collection
    18. CHAPTER 11: Automating Routine Tasks
      1. 11-1. Scheduling Recurring Jobs
      2. 11-2. E-mailing Output from a Scheduled Job
      3. 11-3. Using E-mail for Job Status Notification
      4. 11-4. Refreshing a Materialized View on a Timed Interval
      5. 11-5. Synchronizing Data with a Remote Data Source
      6. 11-6. Scheduling a Job Chain
    19. CHAPTER 12: Oracle SQL Developer
      1. 12-1. Creating Standard and Privileged Database Connections
      2. 12-2. Obtaining Information About Tables
      3. 12-3. Enabling Output to Be Displayed
      4. 12-4. Writing and Executing PL/SQL
      5. 12-5. Creating and Executing a Script
      6. 12-6. Accepting User Input for Substitution Variables
      7. 12-7. Saving Pieces of Code for Quick Access
      8. 12-8. Creating a Function
      9. 12-9. Creating a Stored Procedure
      10. 12-10. Creating a Package Header and Body
      11. 12-11. Creating a Trigger
      12. 12-12. Debugging Stored Code
      13. 12-13. Compiling Code Within the Navigator
    20. CHAPTER 13: Analyzing and Improving Performance
      1. 13-1. Installing DBMS_PROFILER
      2. 13-2. Identifying Bottlenecks
      3. 13-3. Speeding Up Read/Write Loops
      4. 13-4. Passing Large or Complex Collections as OUT Parameters
      5. 13-5. Optimizing Computationally Intensive Code
      6. 13-6. Improving Initial Execution Running Time
    21. CHAPTER 14: Using PL/SQL on the Web
      1. 14-1. Running a PL/SQL Procedure on the Web
      2. 14-2. Creating a Common Set of HTML Page Generation Procedures
      3. 14-3 Creating an Input Form
      4. 14-4. Creating a Web–based Report Using PL/SQL Procedures
      5. 14-5. Displaying Data from Tables
      6. 14-6. Creating a Web Form Dropdown List from a Database Query
      7. 14-7. Creating a Sortable Web Report
      8. 14-8. Passing Data Between Web Pages
      9. 14-9. Viewing Errors for Debugging Web Apps
      10. 14-10. Generating JavaScript via PL/SQL
      11. 14-11. Generating XML Output
      12. 14-12. Creating an Input Form with AJAX
    22. CHAPTER 15: Java in the Database
      1. 15-1. Creating a Java Database Class
      2. 15-2. Loading a Java Database Class into a Database
      3. 15-3. Loading a Compiled Java Class Into the Database
      4. 15-4. Exposing a Java Class As a Stored Procedure
      5. 15-5. Executing a Java Stored Procedure
      6. 15-6. Calling a Java Stored Procedure from PL/SQL
      7. 15-7. Passing Parameters Between PL/SQL and Java
      8. 15-8. Creating and Calling a Java Database Function
      9. 15-9. Creating a Java Database Trigger
      10. 15-10. Passing Data Objects from PL/SQL to Java
      11. 15-11. Embedding a Java Class Into a PL/SQL Package
      12. 15-12. Loading Java Libraries Into the Database
      13. 15-13. Removing a Java Class
      14. 15-14. Retrieving Database Metadata with Java
      15. 15-15. Querying the Database to Help Resolve Java Compilation Issues
    23. CHAPTER 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython
      1. 16-1. Accessing a PL/SQL Stored Procedure via JDBC
      2. 16-2. Accessing a PL/SQL Stored Function from JDBC
      3. 16-3. Accessing PL/SQL Web Procedures with HTTP
      4. 16-4. Accessing PL/SQL from Jython
      5. 16-5. Accessing PL/SQL from Groovy
    24. CHAPTER 17: Unit Testing With utPLSQL
      1. 17-1. Testing Stored PL/SQL Code Without Unit Tests
      2. 17-2. Installing the utPLSQL Unit Testing Framework
      3. 17-3. Building a utPLSQL Test Package
      4. 17-4. Writing a utPLSQL Unit Test Procedure
      5. 17-5. Running a utPLSQL Test
      6. 17-6. Building a utPLSQL Test Suite
      7. 17-7. Running a utPLSQL Test Suite
      8. 17-8. Reconfiguring utPLSQL Parameters
      9. 17-9. Redirecting utPLSQL Test Results to a File
      10. 17-10. Automating Unit Tests for PL/SQL and Java Stored Procedures Using Ant
    25. Index

    Product information

    • Title: Oracle and PL/SQL Recipes: A Problem-Solution Approach
    • Author(s): Josh Juneau, Matt Arena
    • Release date: December 2010
    • Publisher(s): Apress
    • ISBN: 9781430232070