At my organization, our developers require access to the databases; however, I'm not in favor of that. Instead, I use the HTML generation feature of SQL*Plus to generate Web pages for developers and push the information they need out to them. It works very well.
In general, you start your script with "Set Markup HTML ON" and end it with "Set Markup HTML OFF" and everything in between is a standard SQL script, including specifying column widths and headings with the COLUMN statement, totaling numbers with COMPUTE SUM and so on. The "set markup html on" just provides the HTML tables to format the output from a SQL statement. You should also be able to use updates, although there will be no visible output. Of course, you will need to turn off FEEDBACK, VERIFY and other such things which may interfere with your nice HTML output.
I have given one example and picked one that includes most of the useful features. The environment is Unix (Solaris) with Oracle 8.1.7.4, although for this purpose, I suppose it does not make much difference. The application is to display a database healthcheck for a chosen date where the statistics for the healthcheck has been gathered using CRON on the database server. I have included three files: the "HTML file" to kick off the "Shell Script" within Apache and the shell script in turn runs the "SQL script." I have chopped out lots of my SQL script as it is just selecting from various dynamic views. With the shell script, you can make the SQL script dynamic by generating the SQL script within the shell script. You are only limited by your imagination. For example, generating a list of all databases, so the user can choose which database he/she wants the healthcheck for! On to my very simple example:
HTML file: healthcheck.html
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<HTML> <HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META http-equiv="expires" content="Mon, 01 Jan 1970 00:00:00 GMT">
<META NAME="Author" CONTENT="Vasan">
<TITLE>Online Database Statistics</TITLE>
</HEAD>
<BODY BGCOLOR="#ffffff"></body>
<H2> Online Database Statistics </H2>
<TABLE BORDER=10>
<TR>
<TD>
<form method=GET action="http://host/cgi-bin/hcheck.cgi" TARGET="_blank">
<input type=submit value="Database Statistics">
<TD>
<selectname="DBNAME" ALIGN=LEFT>
<option selected value=dbstring0" > Database zero
<option value="dbstring1" > Database one
<option value="dbstring2" > Database two
</select>
</TD>
</form>
</TD>
</TR>
</TABLE>
</HTML>
Shell script: hcheck.cgi
#!/bin/ksh
#
#ident "@(#)$Source$ $Revision$"
# The line below is a sample URL that will be generated by the HTML file
# http://<server-name>/cgi-bin/healthcheck.cgi?DBNAME=dbstring1
# The next four lines are actually in a central file which I include in each shell script
ORAENV_ASK=NO;export ORAENV_ASK
ORACLE_HOME=/opt/9ias/infrastructure;export ORACLE_HOME
PATH=PATH:/usr/local/bin:${ORACLE_HOME}/bin;export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
TWO_TASK=$(echo ${QUERY_STRING} | sed "s/&DAY.*$//" | sed "s/DBNAME=//")
export TWO_TASK
if [ "${TWO_TASK}" = "dbstring0" -o "${TWO_TASK}" = "dbstring1" ]
then
uid="/"
else
exit 1
fi # Do not allow the script to proceed if this is dbstring2
echo Content-type: text/plain
echo
TMP_FILE="/tmp/$$.out"
rm -f ${TMP_FILE}
sqlplus ${uid} @healthcheck.sql ${TMP_FILE} > /dev/null
if [ -f ${TMP_FILE} ]
then
cat ${TMP_FILE}
rm -f ${TMP_FILE}
else
echo "No Records Selected"
fi
rm -f ${TMP_FILE}
exit 0
SQL script: healthcheck.sql
set markup html on
set feedback off
spool &1
set linesize 200
set pagesize 10000
set heading on
ttitle "Statistics on Buffer Hit Ratio"
select trunc((1-(sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets', value,0))+
(sum(decode(name,'consistent gets',value,0)))))
)* 100) "Buffer Hit Ratio"
from v$sysstat;
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,99999999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
d.value "phy_writes",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value))
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where a.name = 'db block gets'
and b.name = 'consistent gets'
and c.name = 'physical reads'
and d.name = 'physical writes';
ttitle "Statistics on Data Dictionary Hit Ratio"
column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100)
"DATA DICT CACHE HIT RATIO"
from v$rowcache;
ttitle "Statistics on Library Cache Miss Ratio"
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
ttitle "Statistics on Library Cache"
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;
spool off
set markup html off
exit
Reader Feedback
Member Mohammed A. writes: Mr. Vasan, you seem to be the proxy for bureaucracy. I am sure you do not have any business reasons to hold the developers from using the DB. If you are concerned about security and performance, think of some creative ways. 1. With your thinking, you will end up doing your developer's job. Leave their job to them. 2. With your policy, you will be delivering code to them instead of them delivering. One of the best practices for IT management -- "Leave it to the professionals."
Member Raj P. writes: This tip gets the prize for oxymoron statement: "Our developers require access to the databases; however, I'm not in favor of that." Just like fish swim freely in water, developers need to have unrestricted access databases.
Member Dennis D. writes: I feel I must defend Vasan. Developers who don't understand database principles can make a real mess of your database. They need access, but should be given their own account and not given the passwords to DBA and important schema accounts. Left unchecked, they can create tables for temporary purposes and not drop them when no longer needed. Then years later everyone is afraid to drop them because some application somewhere may be using them. They can create denormalized tables, tables without primary keys (when the business rules imply primary keys). They can create tables with ridiculus storage parameters. The list goes on. Writing bad code is one thing. Code can be chanaged. Creating a bad schema, and storing millions of rows, is more difficult to fix.
Member Shaun W. writes: With the Sarbanes-Oxley legislation holding corporate officers responsible for database security I would encourage other members to not take Mr. Vasan to task for creating workarounds for keeping developers out of production databases. I thought his approach was novel and proactive. Perhaps the readers would care to go through an audit!
