Skip to Main Content

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. 

-- FILE NAME..: xxx9999.sql

-- PRODUCT....: SYSTEM - module

--

-- CREATION...: Analyst Name, Date

--

-- Description:

--

-- MODIFICATIONS:

--

whenever sqlerror exit 1

SET linesize 80

SET pagesize 60

SET newpage 0

SET verify OFF

SET termout OFF

 

COLUMN TODAY NEW_VALUE xTODAY

SELECT TO_CHAR(SYSDATE, 'fmMonth DD, YYYY') TODAY

FROM DUAL;

 

CLEAR breaks

CLEAR COL

CLEAR computes

 

define dept_name = 'DDDD'

define prog_name = 'xxx9999'

define prog_title = 'TTTT'

 

COLUMN c1  heading 'Column|Head1' format a30 TRUNC

COLUMN c2  heading 'Column|Head2' format a5

COLUMN c3  heading 'Column3'      format 99

COLUMN c4  heading 'Column4'      format 999.90

COLUMN c5  heading 'Column5'      format 999,999

 

TTITLE left xToday -

       center 'University of North Florda' -

       right  'Page ' format 999 SQL.pno skip 1 -

       center  dept_name skip 1 -

       center  prog_title ' - ' skip 1 -

       center prog_name  skip 2

 

BTITLE left 'Job #: &jobnmbr'  -

       right 'Requested by: &jobrqstr'

 

break ON report ON a

 

COMPUTE SUM OF b ON a

COMPUTE AVG OF c ON a

 

SPOOL &so_outfile

 

SELECT   a c1,

         b c2,

         c c3,

         d c4,

         e c5

    FROM table1,

         table2

   WHERE table2_key  = table1_key

     AND f = UPPER ('&var1')

     AND g = '&var2'

ORDER BY 3, 4, 5;

SPOOL OFF

 

Figure 3-2:  Template for SQL*Plus Report

 

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. 

-- FILE NAME..: wsg5230.sql

-- PRODUCT....: Student - General

--

-- CREATION...: Keith Hufford, November 1,2005

--

-- Description:

--   Place AD hold on students with incomplete

--   check list items

-- MODIFICATIONS:

--   2007-10-25 change from report to download

--              adr: 43592 <huff>.

whenever sqlerror exit 1

set serveroutput on size 1000000

set verify off

spool &so_outfile

prompt Set AD holds on students missing checklist items

execute wksghld.p_ad_hold;

execute wksgrpt.p_ad_hold_rpt ('wsg5230','&jobnmbr','&jobrqstr');

spool off

 Figure 3-3:  Example SQL*Plus Script for calling a stored procedure

 @ 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. 

-- FILE NAME..: wsh5210.sql

-- PRODUCT....: Student - History

--

-- CREATION...: Keith Hufford, December 1, 2005

--

-- Description:

--   Reset Transcript print dates so

--   transcripts can be reprinted

-- MODIFICATIONS:

--

whenever sqlerror exit 1

set serveroutput on size 1000000

set verify off

spool &so_outfile

prompt Reset Transcript print dates

update shttran

set SHTTRAN_PRINT_DATE = null,

    shttran_sessionid = null

where trunc (SHTTRAN_PRINT_DATE) = '&printdate';

spool off

 

Figure 3-4:  Example SQL*Plus Script for manipulating data

 

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