CHAPTER 11: Automating Routine Tasks
by Matt Arena, Josh Juneau
Oracle and PL/SQL Recipes: A Problem-Solution Approach
- Title Page
- Contents at a Glance
- Contents
- About the Authors
- About the Technical Reviewer
- Acknowledgments
- Introduction
- CHAPTER 1: PL/SQL Fundamentals
- 1-1. Creating a Block of Code
- 1-2. Executing a Block of Code in SQL*Plus
- 1-3. Storing Code in a Script
- 1-4. Executing a Stored Script
- 1-5. Accepting User Input from the Keyboard
- 1-6. Displaying Results in SQL*Plus
- 1-7. Commenting Your Code
- 1-8. Referencing a Block of Code
- 1-9. Referring to Variables from Nested Blocks
- 1-10. Ignoring Substitution Variables
- 1-11. Changing the Substitution Variable Character
- 1-12. Creating a Variable to Match a Database Column Type
- CHAPTER 2: Essential SQL
- 2-1. Retrieving a Single Row from the Database
- 2-2. Qualifying Column and Variable Names
- 2-3. Declaring Variable Types That Match Column Types
- 2-4. Returning Queried Data into a PL/SQL Record
- 2-5. Creating Your Own Records to Receive Query Results
- 2-6. Looping Through Rows from a Query
- 2-7. Obtaining Environment and Session Information
- 2-8. Formatting Query Results
- 2-9. Updating Rows Returned by a Query
- 2-10. Updating Rows Returned by a Cursor
- 2-11. Deleting Rows Returned by a Cursor
- 2-12. Performing a Transaction
- 2-13. Ensuring That Multiple Queries “See” the Same Data
- 2-14. Executing One Transaction from Within Another
- 2-15. Finding and Removing Duplicate Table Rows
- CHAPTER 3: Looping and Logic
- 3-1. Choosing When to Execute Code
- 3-2. Choosing Between Two Mutually Exclusive Conditions
- 3-3. Evaluating Multiple Mutually Exclusive Conditions
- 3-4. Driving from an Expression Having Multiple Outcomes
- 3-5. Looping Until a Specified Condition Is Met
- 3-6. Iterating Cursor Results Until All Rows Have Been Returned
- 3-7. Iterating Until a Condition Evaluates to FALSE
- 3-8. Bypassing the Current Loop Iteration
- 3-9. Iterating a Fixed Number of Times
- 3-10. Iterating Backward Through a Range
- 3-11. Iterating in Increments Other Than One
- 3-12. Stepping Through a Loop Based on Odd-Numbered Increments
- 3-13. Exiting an Outer Loop Prematurely
- 3-14. Jumping to a Designated Location in Code
- CHAPTER 4: Functions, Packages, and Procedures
- 4-1. Creating a Stored Function
- 4-2. Executing a Stored Function from a Query
- 4-3. Optimizing a Function That Will Always Return the Same Result for a Given Input
- 4-4. Creating a Stored Procedure
- 4-5. Executing a Stored Procedure
- 4-6. Creating Functions Within a Procedure or Code Block
- 4-7. Passing Parameters by Name
- 4-8. Setting Default Parameter Values
- 4-9. Collecting Related Routines into a Single Unit
- 4-10. Writing Initialization Code for a Package
- 4-11. Granting the Ability to Create and Execute Stored Programs
- 4-12. Executing Packaged Procedures and Functions
- 4-13. Creating a Public Name for a Stored Program
- 4-14. Executing Package Programs in Sequence
- 4-15. Implementing a Failure Flag
- 4-16. Forcing Data Access to Go Through Packages
- 4-17. Executing Stored Code Under Your Own Privilege Set
- 4-18. Accepting Multiple Parameter Sets in One Function
- 4-19. Listing the Functions, Procedures, and Packages in a Schema
- 4-20. Viewing Source Code for Stored Programs
- CHAPTER 5: Triggers
- 5-1. Automatically Generating Column Values
- 5-2. Keeping Related Values in Sync
- 5-3. Responding to an Update of a Specific Table Column
- 5-4. Making a View Updatable
- 5-5. Altering the Functionality of Applications
- 5-6. Validating Input Data
- 5-7. Scrubbing Input Data
- 5-8. Replacing a Column's Value
- 5-9. Triggering on a System Event
- 5-10. Triggering on a Schema-Related Event
- 5-11. Firing Two Triggers on the Same Event
- 5-12. Creating a Trigger That Fires on Multiple Events
- 5-13. Creating a Trigger in a Disabled State
- CHAPTER 6: Type Conversion
- CHAPTER 7: Numbers, Strings, and Dates
- 7-1. Concatenating Strings
- 7-2. Adding Some Number of Days to a Date
- 7-3. Adding a Number of Months to a Date
- 7-4. Adding Years to a Date
- 7-5. Determining the Interval Between Two Dates
- 7-6. Adding Hours, Minutes, Seconds, or Days to a Given Date
- 7-7. Returning the First Day of a Given Month
- 7-8. Returning the Last Day of a Given Month
- 7-9. Rounding a Number
- 7-10. Rounding a Datetime Value
- 7-11. Tracking Time to a Millisecond
- 7-12. Associating a Time Zone with a Date and Time
- 7-13. Finding a Pattern Within a String
- 7-14. Determining the Position of a Pattern Within a String
- 7-15. Finding and Replacing Text Within a String
- CHAPTER 8: Dynamic SQL
- 8-1. Executing a Single Row Query That Is Unknown at Compile Time
- 8-2. Executing a Multiple Row Query That Is Unknown at Compile Time
- 8-3. Writing a Dynamic INSERT Statement
- 8-4. Writing a Dynamic Update Statement
- 8-5. Writing a Dynamic Delete Statement
- 8-6. Returning Data from a Dynamic Query into a Record
- 8-7. Executing a Dynamic Block of PL/SQL
- 8-8. Creating a Table at Runtime
- 8-9. Altering a Table at Runtime
- 8-10. Finding All Tables That Include a Specific Column Value
- 8-11 Storing Dynamic SQL in Large Objects
- 8-12. Passing NULL Values to Dynamic SQL
- 8-13. Switching Between DBMS_SQL and Native Dynamic SQL
- 8-14. Guarding Against SQL Injection Attacks
- CHAPTER 9: Exceptions
- 9-1. Trapping an Exception
- 9-2. Catching Unknown Exceptions
- 9-3. Creating and Raising Named Programmer-Defined Exceptions
- 9-4. Determining Which Error Occurred Inside the OTHERS Handler
- 9-5. Raising User-Defined Exceptions Without an Exception Handler
- 9-6. Redirecting Control After an Exception Is Raised
- 9-7. Raising Exceptions and Continuing Processing
- 9-8. Associating Error Numbers with Exceptions That Have No Name
- 9-9. Tracing an Exception to Its Origin
- 9-10. Displaying PL/SQL Compiler Warnings
- CHAPTER 10: PL/SQL Collections and Records
- 10-1. Creating and Accessing a VARRAY
- 10-2. Creating and Accessing an Indexed Table
- 10-3. Creating Simple Records
- 10-4. Creating and Accessing Record Collections
- 10-5. Creating and Accessing Hash Array Collections
- 10-6. Creating and Accessing Complex Collections
- 10-7. Passing a Collection As a Parameter
- 10-8. Returning a Collection As a Parameter
- 10-9. Counting the Members in a Collection
- 10-10. Deleting a Record from a Collection
- 10-11. Checking Whether an Element Exists
- 10-12. Increasing the Size of a Collection
- 10-13. Navigating Collections
- 10-14. Trimming a Collection
- CHAPTER 11: Automating Routine Tasks
- CHAPTER 12: Oracle SQL Developer
- 12-1. Creating Standard and Privileged Database Connections
- 12-2. Obtaining Information About Tables
- 12-3. Enabling Output to Be Displayed
- 12-4. Writing and Executing PL/SQL
- 12-5. Creating and Executing a Script
- 12-6. Accepting User Input for Substitution Variables
- 12-7. Saving Pieces of Code for Quick Access
- 12-8. Creating a Function
- 12-9. Creating a Stored Procedure
- 12-10. Creating a Package Header and Body
- 12-11. Creating a Trigger
- 12-12. Debugging Stored Code
- 12-13. Compiling Code Within the Navigator
- CHAPTER 13: Analyzing and Improving Performance
- CHAPTER 14: Using PL/SQL on the Web
- 14-1. Running a PL/SQL Procedure on the Web
- 14-2. Creating a Common Set of HTML Page Generation Procedures
- 14-3 Creating an Input Form
- 14-4. Creating a Web–based Report Using PL/SQL Procedures
- 14-5. Displaying Data from Tables
- 14-6. Creating a Web Form Dropdown List from a Database Query
- 14-7. Creating a Sortable Web Report
- 14-8. Passing Data Between Web Pages
- 14-9. Viewing Errors for Debugging Web Apps
- 14-10. Generating JavaScript via PL/SQL
- 14-11. Generating XML Output
- 14-12. Creating an Input Form with AJAX
- CHAPTER 15: Java in the Database
- 15-1. Creating a Java Database Class
- 15-2. Loading a Java Database Class into a Database
- 15-3. Loading a Compiled Java Class Into the Database
- 15-4. Exposing a Java Class As a Stored Procedure
- 15-5. Executing a Java Stored Procedure
- 15-6. Calling a Java Stored Procedure from PL/SQL
- 15-7. Passing Parameters Between PL/SQL and Java
- 15-8. Creating and Calling a Java Database Function
- 15-9. Creating a Java Database Trigger
- 15-10. Passing Data Objects from PL/SQL to Java
- 15-11. Embedding a Java Class Into a PL/SQL Package
- 15-12. Loading Java Libraries Into the Database
- 15-13. Removing a Java Class
- 15-14. Retrieving Database Metadata with Java
- 15-15. Querying the Database to Help Resolve Java Compilation Issues
- CHAPTER 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython
- CHAPTER 17: Unit Testing With utPLSQL
- 17-1. Testing Stored PL/SQL Code Without Unit Tests
- 17-2. Installing the utPLSQL Unit Testing Framework
- 17-3. Building a utPLSQL Test Package
- 17-4. Writing a utPLSQL Unit Test Procedure
- 17-5. Running a utPLSQL Test
- 17-6. Building a utPLSQL Test Suite
- 17-7. Running a utPLSQL Test Suite
- 17-8. Reconfiguring utPLSQL Parameters
- 17-9. Redirecting utPLSQL Test Results to a File
- 17-10. Automating Unit Tests for PL/SQL and Java Stored Procedures Using Ant
- Index
C H A P T E R 11
Automating Routine Tasks
Oracle provides methods to schedule one-time and recurring jobs within the database, which is beneficial when you want to automate repetitive tasks and run them at times when a DBA may not be available. This chapter provides recipes to help you get started scheduling jobs (especially PL/SQL jobs), capturing output, sending e-mail notifications, and keeping data in sync with other databases.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.