Oracle CGI Demo

Interfacing Oracle Using a Shell script

If writing a C program seems too long-winded. The above could also be achieved with a shell script. To run it from any of the web servers, the some environment variables need to be set first before using sqlplus in silent mode.

#!/bin/sh

echo Content-type: text/html
echo

# Set up the TWO_TASK environment variables so that the web server can "find" Oracle
# Speak to your local database administrator to find out the current settings
TWO_TASK='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<your_ORACLE-DB_URL>)(PORT=<your_ORACLE-DB_Port_Number>))(CONNECT_DATA=(SID=<your_ORACLE-DB_Server_Name>)(SERVER=DEDICATED)))'

export TWO_TASK

# Now output the standard HTML tags
echo "<html>" echo "<head><title>Oracle Department List</title>"
# reformat the table output using CSS stylesheet, otherwise Oracle output very plain
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 and Employees</h2>"

# sqlplus is invoked in silent mode to list the current departments
sqlplus -s yourOracleUsername/yourOraclePassword <<!

SET MARKUP HTML ON
SET FEEDBACK OFF
SELECT deptno, dname
FROM Dept
ORDER BY deptno;

SELECT * FROM Emp;
QUIT

!

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

SET MARKUP HTML ON is an Oracle SQL*Plus command that outputs the data in HTML format. By default, the table output is a plain white table, but the use of a stylesheet, such as Oracle.css, can change this.

Running the Script

The above can be run:

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).