Batch Programming

This chapter describes the various types of batch programs written at UNF, the reasons for choosing a particular type, and the procedures to follow when creating your program.

Batch programs are designed to process data with little or no operator intervention in contrast to interactive computing where data and instructions are entered while the processing program is running in an online environment. Batch programs are used for reporting, database processing, and file manipulation.

At UNF, batch programs are executed in one of two ways. Either the customer will request a job on demand through Banner job submission, or the program will be scheduled to run at scheduled intervals via the AppWorx job scheduler.

For each batch program you create, you will construct a file on UNIX that will control its execution. There are four basic types of batch programs. The requirements of your program will determine which type of program you will create.

The following section describes each of the basic types of programs and how you should code your UNIX file. In Chapter 7, you'll find details on how to install these programs into the operational environment. Chapter 7 will also provide you with important information about naming standards and other areas with which you should be familiar before coding the types of programs described here.

Types of batch programs

• Comma Separated Value (CSV) download
• SQL*Plus Report
• SQL*Plus script
• UNIX shell script

Each of these types is described in detail in the following sections.

CSV Downloads

One of the most common batch programs written in Enterprise Systems is the CSV download. This type of program is used to deliver data from an ITS-written query in a useable format (Excel) to the customer. The results are extracted from the Oracle database and placed on a network directory for retrieval by the customer.

The CSV download type of program can be used when three criteria are met:

• the results can be retrieved from the database through a standard SQL query
• the results are to be delivered in a CSV or Excel spreadsheet format
• the job will be requested via Banner job submission

Mechanically, this is a fairly simple type of program to code, since your program file will contain only a SQL query. At this point, you do not have to be concerned with how the results your query will be delivered to the customer. This will occur as part of the installation process described in Chapter 7.

Use of parameters in your query will greatly increase the flexibility of your program and allow the customer to tailor the data they retrieve. Be sure to follow proper parameter naming conventions and noted in Chapter 7.

Observe the following when coding a CSV download program:

• Do not end the query with a semi-colon
• Alias columns appropriately as these will translate to column headers in Excel
• Do not use a dash in the column alias
• Double quote any column data that might contain commas or remove them
• Be sure to single quote the parameter values (when they will contain string data)
• Ensure that the file does not contain blank lines

-- FILE NAME..: WSS1002.sql
-- PRODUCT....: STUDENT - SCHEDULE
--
-- CREATION...: Noreen Eberhardt/Earl Hewey
--
-- Description:
-- Create Excel spreadsheet of courses offered for a selected term
-- that have a lab fee.
--
-- Parm: Term (format CCYYMM)
--
-- MODIFICATIONS:
--
-- 2006-12-12 - Created per request id 25029. eeh
--
SELECT ssbsect_term_code AS term, ssbsect_crn AS crn,
ssbsect_subj_code || ssbsect_crse_numb AS course,
REPLACE (SUBSTR (wkssrtn.f_section_title (ssbsect_crn, '&term'), 1, 30),
',',
' '
) AS title,
ssrfees_detl_code AS detail_code,
TO_CHAR (ssrfees_amount, 9999.99) AS amt,
ssrfees_ftyp_code AS feetyp
FROM ssrfees, ssbsect
WHERE ssrfees_crn = ssbsect_crn
AND ssbsect_term_code = '&term'
AND ssrfees_term_code = ssbsect_term_code
ORDER BY course

Figure 3-1: Example CSV Download Program

SQL Plus Reports

The SQL*Plus Report type of batch program makes use of the report generation facilities of SQL*Plus. This permits you to create relatively sophisticated formatted reports with a minimum of coding. The report generator supports page headers, page footers, page and line breaks, and automatic calculation of summary totals and subtotals.

In today's climate, most customers now prefer to receive report data via the CSV download method. However, SQL*Plus reports are a good choice when the program requirements call for formatted output - particularly if the output is to be printed.

To create this type of report, first construct and test your SQL query. Once you have a valid query, you can replace the query in the template (shown in figure 1-2), map your column names to the aliases in your query, and define your control breaks.

Use of parameters in your query will greatly increase the flexibility of your program and allow the customer to tailor the data they retrieve. Be sure to follow proper parameter naming conventions as noted in Chapter 7.

SQL Plus Scripts

The SQL*Plus script is another common batch program you will encounter. There are two forms of this type of program. The most common and also the simplest form is used when you need to call stored procedures in the Oracle database. As such, the UNIX program is simply a conduit used to make the call to the "real" program that is going to do the actual work of the batch process.

Figure 1-3 shows an example of this type of script. In this case, two calls are made to stored procedures in the database. These are preceded by the "execute" verb. The remaining statements are used to establish the execution environment and are typically included in all programs of this type.

@ Use of the prompt verb in your script displays whatever text follows it in the log file of your output.

The second form of this type of program is far less common. In this form, the actual work of the program is done by the script and no call is made to a stored procedure. Use of this type of script is generally discouraged since we prefer that all data manipulation programs reside within the database. If used at all, this type of script should be limited for use on non-operational data as shown in figure 1-4.

UNIX Shell Scripts

The final type of batch program is the UNIX shell script. This type of program is typically used when it is necessary to manage or transfer files, to process data that doesn't reside in the database, or to perform a task that cannot be accomplished with a SQL*Plus script or PL/SQL program.

#!/bin/sh
# -- FILE NAME..: wsu5201
# -- PRODUCT....: Student - Utility
# --
# -- CREATION...: Keith Hufford, May 7, 2006
# --
# -- Description:
# Get BOR highschool file from NERDC
#
# --- get the file -----
cd /home/norf/nfprod
echo "Begin file transfer " `date`
ftp -n nermvs.nerdc.ufl.edu << ENDFTP
quote USER xxxxxx
quote PASS xxxxxx
verbose
get 'bor.xref.highschl' highschl.dat
quit
ENDFTP
#
echo "End file transfer " `date`

Figure 3-5: Example UNIX Shell Script