Programmers new to Ellucian's Banner need tools, references, methods, and a bit of guidance to deal effectively with this complex Oracle database application.  This document is for the experienced application programmer that is new to Banner, Oracle, UNIX, and Linux.  DBAs may find it useful as a resource or reference for programmers they must support.

Tools          BannerArgos      BannerAPEX     BannerAccess  (Using MS Access with Banner)           

Banner Scripts (sorry, it is password protected, request the scripts password from bknox at uaex.edu) 

References and Guides                                

Best Practices - Tips                    

Code Examples                               e-mail Notices - simple SQL solutions for complex Banner Reporting Issues       

       Oracle Dates                             cookie_cutter.sql email from SQL*Plus
         Getting Cross Tabs done using DECODE or CASE

         UNIX file permissions you should know

         Sending email with attachments from unix 

       Sending Word Processor formatting from Oracle or UNIX
       Extract to CSV  -  Extract to Comma Separated Values flat file for Excel  
       Oracle Loader - Getting a text file loaded into an Oracle Table  also see Window External Table info at  Oracle Notes Tip#20
          Using Steve Rea's CES.email.files package makes it easy to send complex emails from PL/SQL  

             Some notes on Steve's CES.email.files for sending email from Oracle: if you are a DBA see: email_files.htm.  If not, see:
             http://betwinx.com/Using_CES.email_files.htm which is a How To use Steve Rea's package. If you are sending lots of messages, then you might also need Tip#19 at
             http://betwinx.com/Oracle_Notes.htm This works very well for us.

 

Cooperative Extension Service

Environment

Disclaimer

 

Tools

 

The language of choice for a new Banner application support programmer is SQL*Plus.  SQL*Plus is the interactive command-line interface for Oracle.  Every Oracle site will have command-line SQL*Plus, your DBA must use it, and it can be used effectively to quickly generate reports.  While you cannot support Banner without some contact with COBOL, C, PL/SQL, and Oracle Forms, SQL is part of each of these.  The quickest way to develop and test SQL is interactively, that is, SQL*Plus.  You can use it for ad hoc reporting and all but the most demanding reporting requirements by using saved SQL*Plus scripts.  Oracle's SQL*Plus, unlike SQL and C, has report generation extensions for Formatting business reports (pagination, totals, control breaks, page titles and footers, etc.).  See Best Practices below for some must have user developed tools.

 

Oracle SQL Developer is a "free", cross platform, graphical version of SQL*Plus which has many useful features more traditionally associated with products such as Toad.  Current versions of this Java based client work well on newer computers.  I would recommend using the latest version available as enhancements have been rapidly added since sqldeveloper's introduction.  The tool has the ability to connect to MySQL, SQLServer, and MS Access databases and has migration aids built-in.  SQL Developer can also be used to enhance APEX development.

References and Guides

 

Oracle SQL*Plus: The Definitive Guide, by Jonathan Gennick

(published by O'Reilly, 1999, ISBN 1-56592-578-5).

This is a great introduction to SQL*Plus, preface - chapter 5.  Also covers SQL*Loader, running PL/SQL from SQL*Plus, and other advanced topics.  (latest edition is updated for Oracle 10g)

 

Oracle SQL*Loader: The Definitive Guide, by Jonathan Gennick & Sanjay Mishra

(published by O'Reilly, 2001, ISBN 1-56592-948-9).  Much more SQL*Loader, including use of user written functions.

 

Oracle Programming with Visual Basic by Nick Snowdon 

(published by SYBEX, 1999, ISBN 0-7821-2322-8).

For MS Access ODBC, Chapter 14 covers Data Access Objects.  One of very few books that adequately covers using MS desktop apps with Oracle databases.

 

I used the following books frequently, some everyday for several years.  In order of frequency of use:

 

UNIX in a Nutshell, by Daniel Gilly

(published by O'Reilly & Associates, Inc., 1999, ISBN 1-56592-001-5).

 

Oracle Database 12c: The Complete Reference, by George Koch and Kevin Loney

(published by Osborne/McGraw-Hill, 1997, ISBN 0-07-882396-X).

This 1300 page reference and guide is the most comprehensive of the Oracle Press Editions.

Oracle12c is the current version. 

 

Steve Rea's Oracle Tips, Tricks, and Scripts

is full of DBA level information.

Larry Holder's DBA Page
More Banner DBA level information.

 

Allen Whipps' PIDM System for identifying multiple Banner PIDMs for an entity.  This is about resolving "Duplicate" PIDMs. (Note that Steve Rea also has notes on this topic.)
 

Resources and Listserv for Banner Developers.  There is a related Luminis Developer Network

 
Jared Still's dump Table to csv file  (If you want more control, see my Extract to CSV example.)

 

UNIX Shells by Example, by Ellie Quigley

(published by Prentice Hall PTR, 1997, ISBN 0-13-460866-6). 

For Programming with the Korn Shell.

 

IBM RS6000 AIX, IBM's Online AIX Reference for this UNIX variant.

I find this more useful than the AIX's man and it includes AIX specific information not available in UNIX (Korn Shell) reference books.

Korn Shell David Korn (AT&T)

 

UNIX SunExpert and S/W Expert articles by Peter Collinson

 

Server Virtualization Management Project Manager is an Open Source friend, Matthias Rechenburg.

openQRM is an open source systems management platform which integrates with existing components in enterprise data centers.

 

C in Plain English, by Brian Overland

(published by MIS:Press, 1995, ISBN 1-55828-430-3).

You need a C not a C++ guide for Banner.

 

Introduction to Pro*C  an Online guide for Pro*C.

 

Programmer's Guide to the Oracle Pro*C/C++ Precompiler an Online reference for Oracle8 Pro*C.

 

MicroFocus COBOL sources for Pro*COBOL answers.  We are currently using NetCOBOL for Linux for our batch processor.

 

Rapid deployment of MS Access connected via ODBC to Oracle as an ad hoc reporting tool for SCT's Banner contains references to a SQL*Plus script to generate MS Access SQL Pass-Through Query code.  This reduces the time required to support MS Access users.

 

Best Practices - Tips

 

Make friends or at least develop a good working relationship with your DBA and your System Administrator (or find a new job).

 

Avoid updating any Banner table directly from your code unless you are populating one of the tables designed for external interface by SCT.

 

The best way to deal with the Banner System’s complexity is to examine the code of a related or similar Report, Process, or Form.  While this code will be in PL/SQL and PL/SQL can be run from SQL*Plus, this should be done only after one is completely comfortable with SQL*Plus. 

 

Mixing PL/SQL and SQL*Plus can be an extremely confusing way to start learning Oracle and will delay your early progress.  This powerful combination should be pursued only after you become productive using SQL*Plus and have a good understanding of the Banner PL/SQL code.

 

Most users think of the database in terms of their input and query Forms.  The Form names are often similar to table names, but this is most often misleading.  You will need a way, form.tables.shl, to know which tables are used by a Banner Form.  (You will probably need your DBA to set up and run this tool.)  This is one of Steve Rea's DBA Scripts that can simplify and speed your problem solving.  You can search the text file created by form.tables.shl to quickly learn which tables are updated by a Banner Form.   I have answered user questions in under 30 seconds using the saved output of this script.  Question that without this script would have taken hours to answer.

 

You need a report that explains the use of fields within a table listcol.sql or tabinfo.sql.  While this information is in the Banner documentation, the ability to list a single table with field-by-field comments is very helpful.

 

A very helpful program by Dave Hill to vertically list the fields in a record is search.sql.  search.sql will display the entire field name with each field's value in a record.  This is a very useful tool for both debugging and general user problem resolution.  I can only give you an indirect link to this tool via SCT's Listserv BORACLE archives March 2000 Item 142.  It is well worth the effort required to find it.

 

You also need a complete SQL*Plus example report, a way to quickly create a new report from the example, and a means of dealing with multiple users and printers.

 

An Approach to Rapid Development or Conversion

 

I telework using quasi interactive terminal sessions.  I reuse snippets of existing code whenever possible.  What is quasi interactive?  I often cut-and-paste between my PC files and a telnet session.   This approach works better for me than working entirely interactively.  I can quickly and accurately write and test both UNIX and SQL*Plus code using tools found on any TCP/IP connected Windows workstation.

(Isn't that risky?  Well yes, it could be.  But, you should always work on a secure connection.  Outside of the FireWall, use VPN, or Putty and WinSCP, or one of the many other encryption tools.  This will largely be the call of your SysAdmin.)

 

For larger code segments, I FTP the code as script files (either script .sql or .shl).  I move between the interactive and GUI environments using the best tool for the task at hand.

 

The main query for many reports can be done in a page or two.  The peripheral code to identify the program, provide elementary in-code documentation, page headings, run date and time, page numbers, and page breaks is often more than the code for the main query.  This peripheral code is largely repeated in every completed report program.

 

I use a UNIX script (report.shl) to generate this peripheral code from an existing complete SQL*Plus script (report_skeleton.sql).  Using a file naming scheme such that output from a report script will be the script file name plus the file extension .lst (and possibly, .lis, .txt, .csv, or .shl).  I found that to write a new report I was repetitively checking for the existence of the new file with the extensions .sql , .lst, .lis, .txt, .csv, .shl.  So, I wrote a UNIX script to do this checking and to create a unique version of the report_skeleton.sql as new file name plus .sql.

 

The UNIX script, report.shl, prompts for a report file name and a short description, which are used to both initially document the SQL*Plus script and title the report.  The new report .sql file is tagged with the current user's User ID and the system date to begin an in-code change log.  This entire process only takes a few seconds, the time to key the new report name and the title.  The report generator script avoids creating report scripts that conflict with existing file names.

 

I FTP this new report script to my PC and replace a single line of code with the separately developed main query.  I must only move the COLUMN statements specific to this new report to convert my development code into a program ready for client review.  This process documents the program and ensures a consistent appearance form one program to the next. 

 

I have found report.shl to be exceptionally useful for converting FOCUS programs to SQL*Plus and for bringing existing SQL*Plus code into standards compliance.   Using this approach significantly reduces the time required to produce a completed professional quality report.

 

UNIX Print File Viewing with MS Windows

 

If you always directly print UNIX report files,

 

SET NEWPAGE 0

SET PAGESIZE 43      (for Landscape or 55 for Portrait)

 

will generally work for most printers.  If some pages are separated by mostly blank pages, reduce the PAGESIZE.

 

This also works fine for viewing with Word or WordPerfect, which recognize the Form Feed control character as "advance to top of page".

 

However, many users do not have Word, WordPerfect, or another word processor capable of using the Form Feed character.  MS Windows does ship with WordPad, a more limited word processor that can serve as a report viewer.  WordPad, one of the Windows Accessories, is not installed by default.

 

To make using WordPad as a report viewer more effective, there are some ways to improve upon WordPad's default behavior.  First, I set my default printer to Landscape since most of my reports are too wide for Portrait.  This does not interfere with Word or WordPerfect settings.  Second, I set WordPad's margins to .25 inch and close WordPad.  This causes WordPad to open with .25 inch margins by default.  Third, I place a shortcut on my Windows desktop for WordPad.  The shortcut allows dragging and dropping report files onto the WordPad shortcut to open the file. 

 

If you prefer double-clicking on the file to open it, just select WORDPAD when you see the "Open with" dialog the first time you open a report (.lst) file.  That will establish the link of extension type .lst with WordPad for all future uses.  Since the report files will be ASCII "text" files, WordPad will open them with a fixed width font (Courier New 10 pt.).

 

We have found WordPad's Search to be a great way to quickly locate an item of interest in a long report.  Viewing the report and only printing selected pages avoids wasting supplies and gets quick results.

 

Since WordPad does not recognize the Form Feed control character, use

 

SET NEWPAGE 1

SET PAGESIZE 51      (for Landscape works on hp4000)

 

Determining the number of lines for different printers, orientations, and paper sizes can be very time consuming by trial and error.  To help with this, I use a setup aid that sequences the report lines using a column of sequence numbers pasted to the actual report file.  It gives results something like:

 

 

     1Report budget_comparison                 Cooperative Extension Service

     2                                

     3Budget Comparison jlbd_prog not equal plbd_prog:

     4         Chart: Z  

     5   Fiscal Year: 2000

.

.

.

    49 

    50 Run: February 11, 2000  02:05:48 pm                           Page No.:     1

    51

    52Report budget_comparison                 Cooperative Extension Service

 

When printed, the sequence number at the top of page 2 of the paper document is the correct value for SET PAGESIZE.  Note that SQL*Plus will be leaving the first printable line of each page blank using SET NEWPAGE 1.  This is due to NEWPAGE serving both to set Form Feed ON/OFF and to set the number of blank lines to print at top margin.

 

I use seq_list.txt a file of sequence numbers 1-200 to paste as a column to the report file.  This is done in UNIX using:

 

paste -d'\0' seq_list.txt report_file.lst > report_file_pasted.lst

 

where report_file.lst is the name of the report file to be aligned.  Viewing and printing the report_file_pasted.lst will help determine the number to be used for SET PAGESIZE.

 

We have individuals using a variety of printers each with unique settings, but an individual almost always uses the same printer for all of their work.  I have placed a current printer specification in a file for each user.  The report program then sets the SQL*Plus PAGESIZE and NEWPAGE setting for the current user each time the program runs.  This allows the report to be run by more than one user without changing the program.

 

We have not seen a pressing need to allow dynamic changes to the user's current printer specification file.   Adding the ability to change one's default printer settings would be an interesting project.  For more information on using this current printer specification method, see getprinter.sql.

 

Code Examples

 

SQL*Plus notes:

! command will pass any host command to the operating system, then return.  !  is used as the shortcut for the HOST command in the AIX environment.  This may be "$" or another character on your host computer.  Many file manipulations in the code examples are performed by using this shortcut to the host operating system:

 

!rm -f filename.ext will unconditionally delete a file.

 

!mv -f oldname.ext newname.ext will unconditionally rename a file.

 

!echo text-string will display the text-string

 

!cat file2.ext >> file1.ext will append file2.ext to file1.ext

 

I also frequently use !mail send email and !sed to edit files from SQL*Plus.

 

@script_file or START script_file will run the saved SQL*Plus script_file.sql.  If done from a script file, @script_file will run script_file.sql and then return to the calling script.  START allows command line arguments to be passed to the called script while @ does not.

 

-- This is a single line SQL*Plus comment.

 

/* This is a

    SQL*Plus comment too */

 

-- report_skeleton.sql          Report_Skeleton_Description

 

SET ECHO OFF

 

-- change log

-- mm-dd-yy USER created using UNIX script report (Report Generator by bknox@uaex.edu)

 

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

 

-- Set Printer Defaults - will be modified by get user's printer specs

SET NEWPAGE 0

SET PAGESIZE 43

DEFINE prnt_code = 'hp4000'

DEFINE prnt_form = 'Landscape'

DEFINE line_count = 43

DEFINE prnt_margin = .25

 

SET SCAN ON

-- get user's printer specs -- begin

 

   !/bin/clear

   !sed "s/string/report_skeleton/g" getprinter.sql > report_skeleton.rpt

   START report_skeleton.rpt

   !rm -f report_skeleton.rpt

   !echo

   !echo "Formatted for:"

   !echo

   !echo "    Printer: "&&prnt_code

   !echo "Orientation: "&&prnt_form

   !echo " Line Count: "&&line_count

   !echo "All Margins: "&&prnt_margin" inch"

   SET PAGESIZE &&line_count

 

-- get user's printer specs -- end

 

!echo " Paper Size: Letter"

SET LINESIZE 128

 

SET SHOWMODE OFF

SET TIMING OFF

SET FEEDBACK OFF

SET HEADING ON

SET TRIMSPOOL ON

SET VERIFY OFF

SET TERMOUT ON

 

-- SET NEWPAGE 0

-- SET ECHO OFF     Suppress Display of commands

-- SET FEEDBACK OFF Suppress Display of record counts

-- SET LINESIZE n   Set line length

-- SET PAGESIZE n   Set lines per page

-- SET SHOWMODE OFF Suppress Display of old and new setting of SQL*Plus system variables

-- SET NEWPAGE  0   Set no lines before TITLE and eject first page

-- SET TRIMSPOOL ON Trim trailing spaces from end of SPOOLed record

-- SET VERIFY   ON  Show each line of the file before and after substitution

-- SET TERMOUT  OFF Suppress Display of output

 

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

 

--DEBUG

-- SET ECHO ON

-- SET SHOWMODE ON

-- SET FEEDBACK ON

-- SET VERIFY ON

-- SET TERMOUT ON

--DEBUG

 

-- Accept run options from operator - place holder

 

-- Time and Date

 

COLUMN today  NOPRINT NEW_VALUE datevar FORMAT A18 TRUNC

COLUMN time   NOPRINT NEW_VALUE timevar FORMAT A11 TRUNC

SELECT TO_CHAR(SYSDATE,'fmMonth DD, yyyy') today,

       TO_CHAR(SYSDATE,'HH:MI:SS am') time FROM DUAL;

 

-- use current date to get asofdate

COLUMN asofdate NOPRINT NEW_VALUE asofdate

SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') asofdate FROM DUAL;

 

COLUMN asofdatealpha NOPRINT NEW_VALUE asofdatealphavar FORMAT A18 TRUNC

SELECT TO_CHAR(TO_DATE('&&asofdate', 'MM/DD/YYYY'), 'fmMonth DD, yyyy') asofdatealpha FROM DUAL;

 

COLUMN ces    NOPRINT NEW_VALUE cesvar

SELECT RTRIM(gubinst_name) ces FROM general.gubinst;

 

COLUMN DB_Instance NEW_VALUE DB_Instance FORMAT A11 TRUNC

SELECT SUBSTR(global_name,1,instr(global_name,'.')-1) DB_Instance

  FROM global_name;

 

!echo 'DB Instance: ' &&DB_Instance

 

SET NUMWIDTH 10

 

TTITLE ON

BTITLE ON

 

SET TERMOUT OFF

 

--                                           COL 56 | IS CENTER

TTITLE LEFT 'Report report_skeleton'         COL 42 cesvar -

       SKIP 2 LEFT 'DB Instance: ' DB_Instance COL 43                   'Report_Skeleton_Description' -

       SKIP 2

 

BTITLE LEFT SKIP 1 ' Run: ' datevar '  ' timevar COL 63 ' Page No.:  ' FORMAT 999 SQL.PNO SKIP 1

 

!rm -f report_skeleton.lst

 

!echo

!echo report_skeleton report beginning

!echo

 

SPOOL report_skeleton

 

SELECT 'Replace this place holder SELECT with your code' FROM DUAL;

 

SPOOL OFF

 

TTITLE OFF

BTITLE OFF

CLEAR COLUMNS

CLEAR BREAK

CLEAR COMPUTE

SET NUMWIDTH 10

SET HEADING ON

 

!echo

!echo report_skeleton report complete

!echo

 

-- Reset Date Format to Banner Standard

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';

 

-- This script is available "as is".  See Disclaimer

 

SET TERMOUT ON

-- end of report_skeleton.sql

 

AIX (UNIX) script file notes:

 

UNIX is case sensitive so keep the file names lower case to keep it simple.

 

#!/usr/bin/ksh on the first line identifies the script as a Korn Shell script.

 

# this is a UNIX comment line

 

exit ends the execution of a UNIX script

 

chmod 644 filename will change the file permissions so that you, the owner, can read and write the file while all others may read only.  This is probably what you want for a SQL*Plus script since it cannot be executed.  It will be run by SQL*Plus not executed by UNIX.

 

chmod +x filename will make a UNIX script file executable by all.

 

variable ="filename" defines variable, which can then be referenced locally as $variable.

 

[[ "$variable" = string ]] will test that variable is equal to string.

 

[[ -f filename ]] will test file filename exists and is not a directory.

 

[[ -z "$variable" ]] will test string "$variable" is zero length.

 

[[ newfile -nt oldfile ]] will test that newfile is the newer file.

 

print string will display string in the Korn Shell.  Similar to echo.

 

read variable will allow you to input a value for variable

 

export variable will make variable global or available for other programs

 

#  report.shl use this link for code file.

      

#!/usr/bin/ksh      

 

#UNIX Script for SQL*Plus Code Generation

 

/bin/clear

echo

echo

echo "To Create a New SQL*Plus Report Program"

echo

echo "Enter Start Name for SQL*Plus Report (no .sql): \c"

read ProgramName; export ProgramName

echo

echo "Enter Report Title (no single or double qoutes): \c"

read ReportTitle; export ReportTitle

echo

echo "Start Name: " $ProgramName

echo

echo "      File: " $ProgramName."sql"

echo

echo "    Report: " $ProgramName."lst"

echo

echo "     Title: " $ReportTitle

echo

 

# test for no file name specified

if [[ -z "$ProgramName" ]]

then

print No Output File Specified -- Report Create Cancelled

exit 1

fi

 

# preclude use of report_skeleton for name of program

if [[ "$ProgramName" = report_skeleton ]]

then

print report_skeleton Cannot Be Used for Program Name -- Report Create Cancelled

exit 1

fi

 

# test for .lst .lis .txt .sql already exist

 

file="$ProgramName.lst"

 

# test for file already exist

if [[ -f $file ]]

then

print $file Already Exist -- Report Create Cancelled

exit 1

fi

 

file="$ProgramName.lis"

 

# test for file already exist

if [[ -f $file ]]

then

print $file Already Exist -- Report Create Cancelled

exit 1

fi

 

file="$ProgramName.txt"

 

# test for file already exist

if [[ -f $file ]]

then

print $file Already Exist -- Report Create Cancelled

exit 1

fi

 

file="$ProgramName.sql"

 

# test for file already exist

if [[ -f $file ]]

then

print $file Already Exist -- Report Create Cancelled

exit 1

fi

 

today="$(date "+%m\/%d\/%y")"

 

# NOTE THAT sed must be on one line for execution see link below for executable code

# replace string example with new file name

sed -e "s/report_skeleton/$ProgramName/g" -e "s/Report_Skeleton_Description/$ReportTitle/g" -e "s/mm-dd-yy USER/$today/g" -e "s/USER/$USER/g" report_skeleton.sql > $file

chmod 644 $file

 

print $file Created

echo

echo

# end of report.shl    UNIX Script for SQL*Plus Code Generation

 

-- getprinter.sql use this link for code file

 

-- this file is never executed directly.  It is input to sed which creates a START file

-- specific to the calling (report) program.

 

-- Get Printer Specification for this USER id

 

-- this specification is intended for reports to be read by WordPad, WP, Word, etc.

 

-- if the name on line one is not equal to the UNIX filename,

-- then this code was created programmatically

-- see the original getprinter.sql file

 

-- see the SQL*Plus program report_skeleton.sql for the placement of calling program

-- statements required to use getprinter.sql

 

-- this method depends upon a file created for each user (see printer_bknox.sql)

-- this file holds the current printer specification for that user and determines

-- the number of lines on a page

-- reference GTVPRNT for the printer control string set up in Banner for that printer

-- (should you wish to print directly)

 

-- bknox@uaex.edu 4-29-99 created

 

SET SHOWMODE OFF

SET ECHO OFF

SET TERMOUT OFF

SET NEWPAGE 1

SET PAGESIZE 51

SET LINESIZE 256

SET TRIMSPOOL ON

SET TIMING OFF

SET TIME OFF

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

SET TTITLE OFF

SET BTITLE OFF

 

-- Set Default Printer Definitions

 

DEFINE prnt_code = 'hp4000'

DEFINE prnt_form = 'LANDSCAPE'

DEFINE line_count = 51

DEFINE prnt_margin = .25

 

SET PAGESIZE &&line_count

 

!rm -f string.prt

 

SPOOL string.prt

 

SELECT 'START '||'printer_'|| LOWER(USER) ||'.sql' FROM DUAL;

 

SPOOL OFF

 

 

START string.prt      -- if not found Defaults will be used

 

!rm -f string.prt

 

-- This script is available "as is".  See Disclaimer http://www.uaex.edu/bknox/

-- end of getprinter.sql

 

-- printer_bknox.sql  use this link for code file

 

-- printer_bknox.sql  Specify Standard Printer for USER bknox

 

SET NEWPAGE 1

 

-- DEFINE prnt_code = 'hp4L'

-- DEFINE prnt_form = 'LANDSCAPE'

-- DEFINE line_count = 52

-- DEFINE prnt_margin = .25

 

-- note that paper size is specified in the report program

 

-- DEFINE prnt_code = 'hp3si_cr1'

-- DEFINE prnt_form = 'LANDSCAPE'

-- DEFINE line_count = 53

-- DEFINE prnt_margin = .25

 

DEFINE prnt_code = 'hp4000'

DEFINE prnt_form = 'LANDSCAPE'

DEFINE line_count = 51

DEFINE prnt_margin = .25

 

-- to use Form Feed Control Character

--SET NEWPAGE 0

--DEFINE prnt_code = 'hp4000'

--DEFINE prnt_form = 'LANDSCAPE'

--DEFINE line_count = 43

--DEFINE prnt_margin = .25

 

-- This script is available "as is".

-- See Disclaimer

 

-- end of printer_bknox.sql   Specify Standard Printer for USER bknox

 

Accept Input from Operator

 

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

 

--Now when Oracle does a date conversion on the two strings in my "store'

--and BETWEEN it uses the session date format rather than the system default.

 

--The following is a typical example of defining, accepting, and

--storing dates in variables:

 

COLUMN begindate NEW_VALUE begindatevar

COLUMN enddate   NEW_VALUE enddatevar

 

ACCEPT begindate CHAR PROMPT ' Enter Begin Date for Selection --(MM/DD/YYYY)-> '

ACCEPT enddate   CHAR PROMPT ' Enter End Date   for Selection --(MM/DD/YYYY)-> '

 

SELECT TO_DATE('&&begindate', 'MM/DD/YYYY') begindate,

       TO_DATE('&&enddate',   'MM/DD/YYYY') enddate FROM DUAL;

 

-- Results: 01-JAN-1999 31-DEC-1999

 

-- Now the substitution will have the four-digit year:

 

WHERE table_date_field BETWEEN '&&begindate' AND '&&enddate'

-- Becomes:

WHERE table_date_field BETWEEN '01-JAN-1999' AND '31-DEC-1999'

 

 

Getting Cross Tabs can be done using DECODE or CASE.

The object is to get a single line for each key value, in these examples Fund.  So, the results would look like:

NHRDIST_FUND_CODE	A611000	A611100	A613000	A615300	A617000	A619210	Other	ROW_TOTAL
13101	918.26	5752.69	0	182700	0	0	4458.78	193829.73
13103	82.87	0	0	0	0	0	0	82.87
13104	7871.76	0	0	0	0	0	1705.48	9577.24
13115	0	0	3633.36	0	0	0	0	3633.36

. . .   except that the totals will sum correctly

Using Oracle DECODE:

SELECT
nhrdist_fund_code,
SUM(DECODE(nhrdist_acct_code,'611000',nhrdist_amt,0)) "A611000",
SUM(DECODE(nhrdist_acct_code,'611100',nhrdist_amt,0)) "A611100",
SUM(DECODE(nhrdist_acct_code,'613000',nhrdist_amt,0)) "A613000",
SUM(DECODE(nhrdist_acct_code,'615300',nhrdist_amt,0)) "A615300",
SUM(DECODE(nhrdist_acct_code,'617000',nhrdist_amt,0)) "A617000",
SUM(DECODE(nhrdist_acct_code,'619210',nhrdist_amt,0)) "A619210",
SUM(DECODE(nhrdist_acct_code,
'611000',0,
'611100',0,
'613000',0,
'615300',0,
'617000',0,
'619210',0,
nhrdist_amt)) "Other",
SUM(nhrdist_amt) Row_Total
FROM
nhrdist
WHERE nhrdist_fisc_code = '2009' AND
(nhrdist_rucl_code = 'HGRS' OR nhrdist_rucl_code = 'HGNL')
GROUP BY nhrdist_fund_code
ORDER BY nhrdist_fund_code
 

Using Oracle CASE:

SELECT
nhrdist_fund_code,
SUM(CASE WHEN nhrdist_acct_code = '611000' THEN nhrdist_amt ELSE 0 END) "A611000",
SUM(CASE WHEN nhrdist_acct_code = '611100' THEN nhrdist_amt ELSE 0 END) "A611100",
SUM(CASE WHEN nhrdist_acct_code = '613000' THEN nhrdist_amt ELSE 0 END) "A613000",
SUM(CASE WHEN nhrdist_acct_code = '615300' THEN nhrdist_amt ELSE 0 END) "A615300",
SUM(CASE WHEN nhrdist_acct_code = '617000' THEN nhrdist_amt ELSE 0 END) "A617000",
SUM(CASE WHEN nhrdist_acct_code = '619210' THEN nhrdist_amt ELSE 0 END) "A619210",
SUM(CASE WHEN
nhrdist_acct_code = '611000' OR
nhrdist_acct_code = '611100' OR
nhrdist_acct_code = '613000' OR
nhrdist_acct_code = '615300' OR
nhrdist_acct_code = '617000' OR
nhrdist_acct_code = '619210' THEN 0
ELSE nhrdist_amt END) "Other",
SUM(nhrdist_amt) Row_Total
FROM
nhrdist
WHERE nhrdist_fisc_code = '2009' AND
(nhrdist_rucl_code = 'HGRS' OR nhrdist_rucl_code = 'HGNL')
GROUP BY nhrdist_fund_code
ORDER BY nhrdist_fund_code

 

 

 

Cooperative Extension Service

 

Cooperative Extension Service is the research and technology transfer arm of our Land Grant Colleges.  We have no traditional students, yet we are the only University with faculty in every county of Arkansas.  We consider the entire state to be our campus and the people of Arkansas to be our students.  You may know us through 4-H (which has been the keystone of Extension in our state for over 100 years), a county extension agent or office, or a score of other ways we improve life through taking research and education to the people of Arkansas.

 

Environment

 

We run Oracle 10g on an IBM pSeries under AIX (IBM’s UNIX).  Our client workstations are Windows PC’s connected via a Novell network.  I work via TCP/IP using cable access (VPN or SSH via Putty/WinSCP).  We support Banner HR, Payroll, and Finance applications that include Oracle SQL, SQL*Plus, PL/SQL, Forms, Pro*C, MicroFocus /Pro*COBOL, and UNIX (Korn Shell) scripts. 

 

We have a large number of SQL*Plus scripts (programs), are converting MS Visual Studio for Web Applications (on a MS NT Server) FoxPro programs to Oracle, import and export data to Word/Excel, Access, FoxPro and a number of other desktop applications.  We transfer/exchange files with banks, insurance companies, the state, and several universities usually via SQL*Plus extracts and SQL*Loader imports.  We are importing data from non-Banner subsystems using Banner’s External Batch Interface.  We support Ad Hoc Reporting using MS Access and Evisions' Argos.

I maintained the website for openMosix, an Open Source Project hosted on .

 

 

Disclaimer

 

Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)

 

http://betwinx.com is oriented to Reporting from Ellucian's Banner product.

http://betwinx.com/BannerTools.htm is oriented to SQL*Plus and UNIX as used for generating bespoken reports.  My personal site, http://betwinx.com, contains related links.