081-087 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Other Tasks

Almost any task can be automated. There are several factors that come into play when deciding whether a task should be performed with a script:

Complexity ”If a task requires a complex set of conditions to perform, then a script may be required to repeat the steps until the task is completed (even if the task will never be performed again).

Repetition ”If the task will need to be repeated regularly, a script might be ideal for the job. This reduces the amount of monotonous work that must be done by the DBA or developers because the script can simply be run as a cron job.

Interaction ” If a task requires little or no interaction, a script can easily perform the task. If interaction is required, it should be required at the start of the task. (This type of interaction often involves setting up parameters for the script.)

Functionality ”If the functionality of the task can be expressed in code, a script can do the job. For instance, a script can double-check account balances with ease, but it would take a bit of work to make a script understand when an account balance that sums properly is still wrong.

Let s consider several tasks and determine whether or not the task should be scripted.

Daily Conversion Of Data From A Legacy System

Every day a set of data must be loaded from the university s mainframe system. Some special applications have been developed on the mainframe, and IS (that s you) hasn t had time to implement those special applications on the new system. Consider these points:

The data from the mainframe always comes in the same format.

The data doesn t require complex interpretation.

The data must be imported every morning before the start of business.

Given these conditions, this task should definitely be scripted.

Preparing Summary Data For Daily Transactions

Every day at 4:30 P.M., IS must prepare a report for the University Accounting Office that summarizes the university s transactions for the day.

The data must be calculated in a standard format.

The data comes from many different locations in the database.

The report has to be generated every day.

Given these conditions, the task should be automated.

Pattern Analysis Of Stock Trends

The Vice President of Finance wants to see a report of trends in the university s investments. The stock prices are keyed into the database by his secretary every morning, but the standard report doesn t help him predict where the investments are going.

You have all the data needed for the report, but it s not 100 percent reliable. (Remember, the Vice President s secretary keys the data in by hand every morning, and everyone makes mistakes.)

The code for the report would have to follow some logical method of predicting trends in the stock market, but a large part of predicting trends is intuition.

The Vice President wants to see this report once.

Unless you ve created a reliable method for predicting where stock prices are going and can translate that method into simple logical steps, you re unlikely to have any success using a script to generate a usable report. (And let s face it, if you had created a reliable method of predicting where stock prices are going, you probably wouldn t be working for a living.)

Documenting Scripts

Scripts play a large role in most systems. If a script performs a task within your system, the script should be documented. A script to restore your database isn t any good if no one has any idea how to run it. The best place to document scripts is within the script s header (or prologue), as illustrated in Listing 3.7.

Listing 3.7 A documented header for a script.

 -- ***************************************************************** -- This script performs an analysis report of trends in student -- grades, broken down by professor. The report should help indicate -- if specific professors are showing unusual trends in grades. -- -- The script accepts the following parameters: -- --    1) The first parameter specifies a department for which the --       script should be run. --    2) The second parameter specifies what level of students should --       be examined. The values for this parameter are: -- --           1 -- calculate grades for all undergraduates --           2 -- calculate grades for all Masters candidates --           3 -- calculate grades for all doctoral candidates -- -- The script is run from the Unix prompt by issuing the following -- command: -- --    sqlplus @grade_trends.report PSYCHOLOGY 2 -- -- -- The script uses these variables: -- --    1) &&MeanGrade -- indicates the mean grade for all students --       in the program. -- -- There are no expected errors that occur when running this script. -- -- The script requires SELECT privileges on the STUDENTS, -- DEPARTMENTS, and ENROLLED_COURSES tables. -- *****************************************************************

The header shown in Listing 3.7 documents several important aspects of the script:

Purpose ”A statement about the general functionality of the script.

Parameters ”The parameters expected when the script is called are documented. An explanation is given for the use of each parameter within the script. An example of calling the script from the command line is given.

Variables ”Any variables used within the script are documented, and the use of the variables is discussed. While this isn t critical to the person who runs the script, it will be critical to the person who has to update the script down the road.

Errors ”Any errors that can occur while running the script are documented. If possible, reasons are given for these errors, and workarounds are documented.

Account privileges ”The privileges required to run the script are specified.

Leftovers ”Any special information about the script is documented.

Scripts for your system should be in version control with the rest of the code; no one wants to have to re-create the backup script for your database or the report that took a week to write and debug. It s especially critical that test scripts (and their results) be stored in version control, because, at some point, the tests will need to be executed again.

Table of Contents