HTML from SQL

Don't want to give developers access to your databases? This script can help you push them updates via HTML instead.

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!

This was first published in May 2004

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close