Using
SQLPass-Though Queries for Banner Reporting
by Bruce Knox bknox
@t uaex.edu
Oracle Database Programmer/Analyst
date last updated 05/13/2009
At some point you need the Oracle Guys!
Use MS Access for what it does well, but always remember that the real database is Oracle based. That means you have the very best relational database and some very powerful tools available to feed your MS Access or Office applications.
Feed How?
You might think that a report or text file extract is all you can reasonably get from your Oracle programmers, but that is only the beginning.
The fastest way to generate
a flattened version of Banner is to create new Oracle Tables.
And any table in Oracle can be available to you via ODBC.
There are also Oracle Views and Functions that can present a dynamic view of Banner. Again, very available to MS Access.
If you have a troublesome MS Access Query, present the task to your Oracle staff. They will find your MS Access Queries to be most helpful in designing an Oracle solution.
What they generally cannot do is give you a report as presentable as one you can get from MS Access or Excel. So get the data with Oracle and Report with MS Office.
Just things to consider:
Most Banner Reports can be set up to run unattended (including providing the runtime parameters)
An extract from the Server can have the same format as a .txt or .csv file. The difference is that Excel can open the .csv file with a double-click vs. an Import for the .txt file.
Scheduling Jobs, changing file names, copying files to other computers, sending email reports, encrypting files, making decisions (yes they can) are all normal tasks performed with Servers.
Approval Notices email (password required, request from bknox@uaex.edu)
Direct Deposit email (password required, request from bknox@uaex.edu)
PO Scripts (password required, request from bknox@uaex.edu)
openMosix, an Open Source Linux Clustering project to which I contributed Website and PR work.
Using SQL*Plus for Banner Reporting
This is a personal site maintained by Bruce Knox ( bknox at uaex.edu ) documenting University of Arkansas, Division of Agriculture, Cooperative Extension Service IT projects.
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:)
Any University of Arkansas Division of Agriculture website Disclaimer terms
found in conflict with terms of this disclaimer shall over ride and replace
the conflicting terms found herein.
Other personal pages/sites that I
maintain:
http://www.openMosix.org the
website for the openMosix (Open
Source) Project hosted
on .
Note: The openMosix Project officially closed March 1,
2008. The source code and mail lists archives will continue to be
available on SourceForge as reference materials. Archive
Links are: FAQ HowTo Wiki SourceForge
Most of the works of art on my pages other than the Extension banner
are
used by permission of J.
Wilson Spence.
http://betwinx.com is
my personal site. betwinx?
This site is largely oriented to Oracle Reporting Solutions in the context
of generating bespoken reports from SunGard Higher Education's Banner
product. My university site is a more restrained version which cannot
contain endorsements of products or companies.
(I maintain a number of special topic pages with links from this primary
site.)
Near mirror at http://www.uaex.edu/bknox/ which is my university work site.
page mirror http://www.uaex.edu/bknox/Summit_2003_p09.htm All the Tools