Oracle CGI Demo - FORMS

Typically in a Web application you will want the ability to query the database on certain parameters only. The commonest use of the CGI mechanism is to provide a back end for HTML "FORMS". This maybe done with either a Shell script or a C program.

Producing Forms Using a Shell script

Two Shell scripts are needed this time: listDepts.cgi and getDept.cgi, plus a SQL script listDetails.sql.

listDepts.cgi gets the current list of departments in the database. The script initially sets up the environment variables needed so that the web server can find where Oracle is:

listDepts.cgi
#!/bin/sh

echo Content-type: text/html
echo

# Set up the environment variables so that the web server can "find" Oracle
ORACLE_HOME=<YourOracleHomePath>
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH
TWO_TASK='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<yourOracleHostName>)(PORT=<yourOraclePortNumber>)
)(CONNECT_DATA=(SID=<yourOracleDatabaseName>)(SERVER=DEDICATED)))'
export TWO_TASK PATH

# Now output the standard HTML tags
echo "<html>"
echo "<head><title>Oracle Department List</title>"
# reformat the table output using CSS indoc
echo "<link rel=StyleSheet href=\"Oracle.css\" type=\"text/css\">"
echo "</head>"

echo "<body>"
echo "<h1>Accessing Oracle from a Shell Script</h1>"
echo "<h2>List of Departments:</h2>"

echo "<form ACTION=\"getDept.cgi\" METHOD=GET>"
echo "<select name=dname>"

# sqlplus is invoked to list the current departments
sqlplus -s scott/tiger<<!

SET MARKUP HTML ON
SELECT '<option>' || dname || '</option>' FROM Dept
ORDER BY dname;
quit
!

echo "</select>"
echo "<input type=SUBMIT>"
eco "</form>"

# Finally provide the closing tags
echo "</body></html>"

 

When the user chooses the appropriate department, the submit button will run getDept.cgi:

getDept.cgi
#!/bin/sh

echo Content-type: text/html
echo

# Set up the environment variables so that the web server can find Oracle
ORACLE_HOME=/oracle/oracle/product/10.2.0/db_2
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH
TWO_TASK='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<yourOracleHostName>)(PORT=<yourOraclePortNumber>)
)(CONNECT_DATA=(SID=oracle)(SERVER=DEDICATED)))'
export TWO_TASK PATH

# Set up the standard HTML headers, etc.
echo "<html>"
echo "<head><title>Department Employees</title></head>"
echo "<link rel=StyleSheet href=\"Oracle.css\" type=\"text/css\" >"
echo "<body>"
echo "<h2>Accessing Oracle from a Shell Script with a FORMS parameter</h2>"
echo "<h3>Department:</h3>"

# Find the details for the chosen department
# $QUERY_STRING is set when the Submit Query button is pressed in listDepts.cgi

sqlplus -s yourOracleUsername/yourOraclePassword <<!

start listDetails $QUERY_STRING

!

# Output the closing tags
echo "</body></html>"

A stylesheet has been used, Oracle.css, to improve the default table format. By default, Oracle outputs a plain white table.

Finally, listDetails.sql queries the database, the data is outputted using Oracle's HTML format:

listDetails.sql

SET FEEDBACK OFF
SET VERIFY OFF
REM SUBSTR is used to remove the "dname=" in $QUERY_STRING
SET MARKUP HTML ON

REM Show the Department details first

SELECT deptno, dname FROM Dept
WHERE dname = SUBSTR('&1',7,(LENGTH('&1')-6));

REM Now output the employees for the chosen department

SELECT empno, ename, job, hiredate, sal, NVL(comm,0)
FROM Dept D, Emp E WHERE D.deptno = E.deptno
AND dname = SUBSTR('&1',7,(LENGTH('&1')-6))
ORDER BY ename;

Note, currently students can not run cgi scripts against the mi-linux webserver.
If you want to try these out, you would need to try these against a different webserver (e.g., a home webserver).

Click here to run the program.