By using the SQL*PLUS ‘@http’ command, we can access scripts that are located in a central repository – in this case residing on a machine where the 10g Oracle Apache Http Server is running. Rather than have local copies of SQL scripts residing on all the client machines, we can store them in one location and then run them from any client. If we need to add new scripts or edit scripts, similarly we do it in just the one location.
These scripts can be called from all kinds of clients including tools like Toad or from any server in the same network as the Apache web server.
The set up is very straight forward.
In the HTTP server Oracle Home we create a sub directory to store the scripts.
$:/u02/oradata/product/apache/Apache/Apache/> mkdir scripts
We now copy all the *.sql files containing the scripts which we would like to run in this location
We then add the alias ‘scripts’ in the http.conf file
$:/u02/oradata/product/apache/Apache/Apache> cd conf
$:/u02/oradata/product/apache/Apache/Apache/conf> vi httpd.conf
Alias /icons/ “/u02/oradata/product/apache/Apache/Apache/icons/”
Alias /javacachedocs/ “/u02/oradata/product/apache/javacache/javadoc/”
Alias /i/ “/u01/oracle/product/11.0/apex/images/”
Alias /scripts/ “/u02/oradata/product/apache/Apache/Apache/scripts/”
Using the opmnctl stopall and startall commands we restart the Apache web server so that our changes can now be picked up.
We then launch SQL*PLUS client from any machine – connect to a database and then call the stored scripts via http as shown below .
gnu1d:/u02/oradata/product/apache/Apache/Apache/conf> sql SQL*Plus: Release 220.127.116.11.0 - Production on Tue Apr 6 14:39:43 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 22.214.171.124.0 - Production SQL> @http://devu007:7782/scripts/active_users.sql O/S Oracle Oracle Login Oracle Session Session Serial ID User ID Status ID No Process ---------------------- ------------------ -------- ------- ------- -------- oracle SYS ACTIVE 10 14638 757982